Hi guys, wondering if you can help because no one I've talked to can so far..
I've got a form with Room information on, and on it a subform. The subform contains information from 2 tables (Items and Audit). Items is basically a list of all the different combinations of furniture item that can exsist (a damaged green chair, a large new desk etc) and as theres only about 70, and 1000's of bits of furniture I decided having common entries would be a better method to save on memory.
Right, the Audit table contains a Room# field and an ItemID field, obviously each furniture item within the Items table has a unique ItemID. The subform is created displaying the information from the Items table, so it tells you that its a damaged green chair rather than just giving you the number 3, and has the ItemID hidden, just for linking purposes.
This works fine for displaying information, but I can't make it so the user can add a new record. If a new furniture item needs to be added then they want to be able to type (new blue chair) and not the ItemID number, so the subform somehow needs to lookup the ItemID from the information typed in as a new record, and then write this ItemID into the Audit table, before refreshing the form. Obviously though at the minute, if anything is added to the subform, it just goes onto the bottom of the Items table, and hence repeats whats already there, which is silly.
I'm just not sure how to go about doing it and would appreciate any help or advice that can be offered. If I've not explained clearly enough please let me know.
Thanks!
I've got a form with Room information on, and on it a subform. The subform contains information from 2 tables (Items and Audit). Items is basically a list of all the different combinations of furniture item that can exsist (a damaged green chair, a large new desk etc) and as theres only about 70, and 1000's of bits of furniture I decided having common entries would be a better method to save on memory.
Right, the Audit table contains a Room# field and an ItemID field, obviously each furniture item within the Items table has a unique ItemID. The subform is created displaying the information from the Items table, so it tells you that its a damaged green chair rather than just giving you the number 3, and has the ItemID hidden, just for linking purposes.
This works fine for displaying information, but I can't make it so the user can add a new record. If a new furniture item needs to be added then they want to be able to type (new blue chair) and not the ItemID number, so the subform somehow needs to lookup the ItemID from the information typed in as a new record, and then write this ItemID into the Audit table, before refreshing the form. Obviously though at the minute, if anything is added to the subform, it just goes onto the bottom of the Items table, and hence repeats whats already there, which is silly.
I'm just not sure how to go about doing it and would appreciate any help or advice that can be offered. If I've not explained clearly enough please let me know.
Thanks!
