Page 2 - Forms - Form not pulling/showing records as expected

I think the reason you have had difficulty updating the Quantity is that you have a composite primary key in the Inventory table, so if all values of those five fields are not present in the query, it cannot find a record or add a new record. Again, this kind of anomaly is much easier to spot if you show all the fields in the QBE grid, then you will be able to see what fields are coming from what tables. Again, a Microsoft "advantage".

I didn't encounter any problems in updating the quantity, using your Form just as it is.

I've fixed the first several combo boxes on your Form, and cleaned up your Query, and re-established Relational Integrity on all your relationships. The problem there was that you had nearly all the Join Types wrong in your Relationships. It's the difference between "All records from A and only those records from B that match" and "All records from B and only those records from A that match". It's always a little confusing. You just have to stop and think about what makes sense for each Join.

The database is too large (even after compacting) to post here, so you can download it from Download TAGdb.zip

See if this helps get you going again.

That's as I expected - as I stated in my first posts.
It's easy for someone with no database history... wizards and built-ins and forms- oh my... (lol)
But what they've done is completely obfuscate any normal delineation between the database system, the user interface system or OLAP tools, and any SQL stored procedures and ODBC connections that they work through.
...for that matter, it seems that Access is all but trying to discourage the use of SQL:

I don't believe I used the query builder (definitely "new to a database guy"... something that leaves you scratching your head and saying "durrrrr.... that ain't SQL!!" lol) - I am fairly certain that it opened by default, and I switched to SQL view to see if Access had created a SQL mess (like when you use tools like Dreamweaver or Frontpage to create a web page, and look at the abhorrent mess of HTML code it generates ). I *do* remember trying to change the "layered" way it structured my joins (like the rube-goldberg way you have to construct a complex "IF" statment in Access), but apparently then I wasn't speaking Access's dialect of SQL any more...

I'm sure what they are trying to do is make it visual - I could see how it might be possible to look a little more intuitive for simplistic type queries I suppose... but it's very strange indeed given that SQL is the standard (and largely standardized, for that matter) language for DB environments.
Just an observation, as an Access newbie.

But anyway - enough reiterating why I can't see the delineations through the fog I'm sure...

This was VERY helpful, and I may have time to get back on this project again (backburner again, in part because I haven't been able to show progress, and this kind of development effort just never gets the kind of love that production work gets! ):

Thanks, that's probably exactly what I need to know to get this thing working, and that's job #1 at the moment.
Only 100 items!??
Holy @#$% batman!
With DB2, I had a table with nearly 500 million telephone contact records (large call center business), and when properly indexed yielded sub-second response times...
With SQL Server, routinely queried large tables (tens to hundreds of thousands of rows) that were not supported by at least one "ideally would be there" index, and at least got results back in a few seconds...

I know Access is just a desktop tool, but is it really THAT scaled back?

More importantly - can't I simply index columns to support the drop-downs (to facilitate the queries behind-the-scene)?

I can't predict how many rows I'll need - but that's certainly a low enough ceiling to hit your head on with just a tiny hop...

Thanks - you've been a huge help already, and I haven't had a chance to check your zipped version out yet.

I had no problem updating either - my concern is that we actually do more development and prototyping here than repeat production work, and as such the materials manager will be inserting more than he will be updating. Updating works, inserting I'm sure brings the parts-to-inventory table dependency into the equation - that's something I would expect to fight on any DBMS (although, I'd have certainly expected to at least work-around it by disabling referential integrity enforcement ).

It's more of an embarassingly amusing error at this point that I dragged and dropped my joins in the wrong order... I went from child to parent (although I thought one-to-many symbology was there?) and "wasn't there yet" (hence, disabled ref int enforcement ). It's a sign of the development status... slow development at this point.

Well, I should have time today to get back on track, at least a bit.

I'll certainly update with my results, and thanks again!

Checked it out, and I do have a proper list of all potential values in each check-box (as though tied to a query "select distinct [fieldname] from [tablename]") - and the select-distinct logic would explain extra processing that would choke a form that was referencing a table that has more than 100 rows to filter through - dynamically - for several fields (as many drop downs as are on the form)... not quite what I thought.

[to others - in THIS form, if I select the drop-down corresponding to the PK field, I have a list of all PK's - perfect! When I select another PK however, the other fields in the form remain what they were... although for each of them I can select from a list of their current distinct values also.]

The logic I was picturing is a little more simple:
Use the part number selection box to select the record, and the other fields auto-populate with that particular record.

This form isn't for a DBA, it's for an end user, and I'm afraid it would be confusing if they selected the record for a particular part, but the other fields didn't change to reflect that record... in fact I'd be afraid they'd hit update and essentially overwrite a record with incorrect values.

I'm hoping to avoid (but I'm getting the feeling...) having a two-part form - the first one to select a part number, which then opens a form for THAT record...
...it's a simple/common type of form outside of the Access world - but obviously my knowledge of Access's capabilities is downright shameful - I may be on a wild goose hunt...

Or [summoning optimism... summoning optimism...], it may simply be that I didn't state this clearly, and so it was done that way because that was the understanding of need?
If this is a basic thing for most update forms, I can't imagine Access wouldn't do it too.

Edit:
I see that for "row source" I can invoke the query builder, rather than just select a table.
...I would then speculate that I could create this query:
"Select [fieldname] from Parts where MfgPN = [MfgPN currently selected in the form]"

(Parts is the table and MfgPN is the primary key of the table - as well as the drop down in the form I'd like them to select)

How do I reference the value of another form element? Syntax? Must be possible, given this query builder option...

Let's simplify this problem.
Too many words previously:

Pretend I have a form (named InventoryManagement) with two comboboxes:
MfgPN - the part number in the Parts table
Manufacturer - the manufacturer's name associated with that part

What I want is to have the inventory manager select the part number in the drop down box, and have the associated manufacturer's name appear in the Manufacturer combobox.

In the Row Source property, I added the following, partly using the Query Builder:

SELECT Parts.Manufacturer FROM Parts WHERE Parts.MfgPN = forms!InventoryManagement!MfgPN

Sadly, this doesn't work - is it simply syntax?
I had found something that showed the hierarchy in that order, and exclaimation marks to delineate.

ANY help is appreciated, and thanks again - I feel like I'm close (and yet SO far!)

I started a new thread to close this one:

http://forums.aspfree.com/microsoft...eld-241294.html

Thanks!