Forms - Form not pulling/showing records as expected

Although I spent years as a DBA on SQL Server and DB2 environments, I'm new to Access, and struggling with the built-in components such as forms.
Nice concept, and I've got the perfect mini-size application that would be perfect for Access - if I can figure out how to get around my roadblocks!

I've created a form that is bound to a query.

I've used drop-down combo boxes for all the forms, as my intent is to create a form that an inventory manger can use to both update quantities on existing inventory items, as well as add new inventory items.
I'm sadly having issues with both...

Envision a database filled with inventory items - let's say, with part numbers ranging from ABC001 to DFG999.

I'd like the inventory manager to drop down the "part number" combo box, scroll to, and select his part number, which would refresh the fields in the form with that part number...
...right now, when I click the combo box drop down arrow - the drop down is completely empty! (yes, I have several test parts in my inventory table)

Also, when I do use the form to create a new inventory item, for some reason I can't enter data into the "quantity" field. I expect this must have something to do with my join, between the "parts" table and "inventory" data which contains the quantity column. I actually disabled "enforce referential integrity" and still am seeing the issue.

Any help on these two issues is greatly appreciated! As well as any terms to search by (here and elsewhere), field attributes to check, etc...
Most sincere thanks!!

You didn't mention what version of Access you are using but it sounds like a known problem with Access 2003 after applying SP3. Has the HotFix also been applied? http://support.microsoft.com/kb/945674 And the symptom you describe requires the removal of anything in the Format property of the field in the table with which you are working.

I suspect the problem has less to do with Access than it does my own unfamiliarity of forms...
I will take a look at the format property (assuming you mean on the "quantity" column?), but the oddity was that all other fields 'took' data without issue. As I mentioned previously, this column is different than the others as it exists in another table (equi-joined in the query the form is built from).

I believe it is Access 2003 that I have on my laptop - I don't believe I have SP3 applied, but I may have. Certainly I don't have any HotFix applied - I will do so as soon as I'm back on my laptop!

Thanks for the help so far - any suggestions for something I may not know?
I suppose this answer begs this question:
Should I expect that I can select records via the drop-down in a combo box?
...if so, should I expect the other columns to move to that record as well (as would happen if I used the navigation buttons at the bottom - which I don't want to use)?

Thanks again, so far!

You can get your form to move to the record you choose in the ComboBox. Actually the ComboBox Wizard will create this cbo for you on a bound form.

Here's a simple example.

I absolutely will - I'm juggling projects and sadly this one is closest to my heart, but doesn't get the attention I'd like it to.
I have my laptop today, I may attempt the patch this afternoon - sorry for the suspense - I know *I'm* sure hoping "good things come to those who wait"! :-D

The reason you "can't enter data into the 'quantity' field" is... In Access when a form is built off of a query that involves ANY joins to another table, it will not allow you to alter the data in the underlying tables. If the form was built off of query with just one table(containing criteria) it would allow you to alter the data in the underlying table.

The answer for the lookup, as stated by another user, is to use the combo box wizard, which can be accessed from your toolbar. Good Luck.

Just for the record, Joins in a query do *not* necessarily make it non-updateable. Here's a link that describes most of the reasons for a non-updateable query. http://allenbrowne.com/ser-61.html

I stand corrected. Thank you!

Well, a straight week of urgent new job launches meant I couldn't touch this...

But this week, I applied the hot-fix, and sadly - nothing.
My drop-down combo boxes still show absolutely nothing but an empty rectangle when I click them (any of them - not just my key field - same behavior as before).

The format property is blank on all of my combo boxes (I didn't change anything - that must be the default value).

I haven't had a chance to take a look at that link, for rules on joined tables - they are simple equi-joins, and I would think in the state that I last left this database (read: I turned off all referential integrity enforcement) that I'd at least have enabled that... but learning rules like those I'm sure will go a long way to making ME effective and understand this Access environment - thank you very much for that!

Maybe what would be helpful would be if I post this database out there on the web, if anyone feels like opening it, and the form in question?

I've uploaded the database, here:
[interesting, the forum blocked my URL - let's try to get tricky]
www . geocities . com / geolemon / TAGdb.mdb
[sorry I couldn't create a direct link - forum 'new user' rules?? You'll have to remove all those spaces ]

The form in question is - "InventoryManagement".
Right now, this thing is so small - simply a set of tables that aren't finalized, only a couple of them have test data in them, I haven't even finalized my joins.

I do see one potential design issue already (just to underscore the real level of development status ) - I want to have separate quantities recorded for a single part number that might be in different statuses, or locations - I'm not sure how my form would handle that, a drop-down by part number [as one part number will potentially have two records, in the query result, after processing the joins].
However, I doubt that's my issue at the moment - unless Access is built to recognize/prevent issues like this before they actually arise... that seems unlikely (especially as that might be acceptable or even desirable in some cases).

Note:
I was able to use the form to input the data for these test records. I was not able to initially input a quantity. Pulling the record back up, after initially creating it, I was able to update the record to add the quantity. I'd like to avoid this behavior as it would never be acceptable to an end user.

Maybe this will help, rather than all the back and forth - something is bound to have flashing neon lights here, for an experienced Access user.

No peanut gallery on the design at this point please - remember, I'm roadblocked in my design process at this point, way back at familiarizing myself with Access to even determine certain feasibilities.

Thanks in advance!

I did look at that link (thank you), and I think you will see that there's nothing in my query that would prevent it from being updatable.
But the quantity column (which is an exclusive element from one particular joined-in table) remains unavailable on initial insert - but then perfectly updatable thereafter.

That behavior is what led me to turn off the referential integrity enforcement - I presumed that it wouldn't "like" inserting the quantity into that joined-in table because a corresponding key value didn't yet exist in the parent table.
Turning off referential integrity obviously didn't solve it - there is clearly something else I am ignorant of a this point.

So far, everything I have learned here leads me to believe "this should be do-able!" - so thank you for your help so far!

Again, the DB location:

www . geocities . com / geolemon / TAGdb.mdb

Any help is much appreciated!

Just for the record, it is the Format property in the *table* that causes the blank cbo's.

Hi, You can update records if you use the record selectors at the bottom of the form to go to a record you can then select the field you want to update the over type it. This is saved to the table.

All the combo boxes on you form are linked to the form query as their source property (this is why they are blank). They should be linked to the tables that hold the data you want to populate the combo box with.

Welcome to the wide, wonderful world of Access! Coming from your background, it is not surprising that you find Access a bit hard to get familiar with. Microsoft has made everything so ridiculously easy that it's confusing to someone who has had to wrestle with the component parts of other database systems. I'm only being slightly facetious.

To start with, your combo box lists are blank because you didn't specify any Row Source property for them. Check out that property in the Data tab of their property sheets. A combo box (and a list box) has several critical properties that work together to give it the behavior you need:

• Control Source, which is what field in a table or query they are bound to--that is, what value will be displayed and what field will be updated.
• Bound Column, because combo boxes can be multi-columned, but can only be bound to one column.
• Column Count, which specifies how many columns to draw from the Control Source.
• Row Source Type, where you can select either a Table/Query, a Field List, where you manually enter a list of items, or a File List, which allows the user to choose a file from the file system.
• Row Source, which is where the data comes from that fills the dropdown list.
• Column Widths, where you can specify widths of the columns displayed in the dropdown list, any of which can be 0.0", making them invisible.

As a point of information, performance of dropdown lists drops significantly as the number of list items increases. As a rough rule of thumb, if you anticipate more than perhaps 100 items in a list, you should probably consider alternative ways to choose an item.

Next, you have neglected to bring any fields down into the grid section of the Query Builder for your query. I see that Access uses an asterisk wildcard in the SQL in that situation (which I didn't know, because desired fields are routinely brought down, even if you highlight an entire table and drag all the fields into the grid). But I would recommend that you show all of your fields in the grid, and for a particular query, only bring down the fields that are pertinent (for performance reasons, at least in theory). This also is the only way you can add Criteria (WHERE clause items) in the Query Builder. As you may already know, you can always view the actual SQL that Access generates for you by going to SQL View in the View menu or using the View button on the Toolbar.