Populating a field from a 2nd table based on data in previous fields (1 Viewer)

[sychoangel]

Hello J I’m using Access2007…
I have a Database with 2 tables... One is where the primary information is stored, and the second has a list of reserved IDs.
I need to set up a form where based on the values of the first 2 fields , it will look at the second table & find the next available ID that matches the criteria and then populate the ID field of the table with a value (and also store the ID in the table)
An e.g. of the Form filled in with the criteria:
Colour: RED
Size: Sm
ID: _______
So then the ID needs to be automaticall populated (and stored in the primary table) from the second table with the next available value that meets the criteria:
Colour Size ID
Red Lg RL01
Red Lg RL02
Red Sm RS01
Red Sm RS02
Red Sm RS03

I am normally good with databases, but have a mental block & don’t know where to start…

 

[wazz]

- create a query based on the second table;
- turn totals/grouping on;
- set criteria for color = the color field on your form; don't show;
- set criteria for size = the size field on your form; don't show;
- set the 'total' row for id = max; show;
- do a DLookup of id and add 1.