I need some help with fields

Bachfile

Registered User.
Local time
Today, 11:59
Joined
Jul 4, 2002
Messages
41
Hi, I am designing a database for a small museum with the purpose of cataloguing all of the artifacts in the museum using hard-copy records. I've got two main forms to enter the data - one form to enter a donor, their name, address and phone number and another to enter all of the information for the artifacts that each donor has donated. The donor form has a subform on it that displays all of the artifacts donated by that specific person, but it is not ergonomically useful to use it to enter artifact data. Now the problem.

I want to have the donor name, address AND phone number show up in the artifact data entry form, but as it stands, only the donor name shows up thanks to the relationship between the donor name field in both forms. I tried to create relationships for the other two fields, but that has proven fruitless. IS there a way to get the field to look up the value (whether it be the phone number or the address) in the other table using an expression in the control source field? I don't have any idea on the syntax or how I would go about it.

Thanks a lot.

Bachfile
 
A combo box based on a query which contains all the fields you require is the easiest way, the wizard will build it for you
 
You can create an "AutoLookupQuery".

1. In the QBE grid, add the Artifact table and the Donor table.
2. Join the two tables on DonorID.
3. Select all the fields that you need from both tables.
4. Base your Artifact form on the above query.
5. Use a combobox for DonorId. When a donor is selected from the list, his details will fill in.
6. Make sure that all the donor fields, except the ID, are locked so that they cannot be accidentally changed on this form.
 
Thanks for the suggestions. Just one more thing. I really don't want to create a new form. Is there any way to alter the control sources to read the record of the donor's address and phone number from the other table, or is that impossible without doing this query stuff? Just curious. I am definitely sure that the aforementioned methods would work, I was just hoping I could change a couple field control sources somehow to fix it. Anyways, thanks a lot for your help guys, I really appreciate it!

Bachfile
 
You don't necessarily need to create a new form to use the query that I suggested. Create the query. Test it and make sure the autolookup works. Then open the form in design view and change the recordsource (on the data tab) so that it points to the new query. Then by changing the control source of any unbound control to a column from the new recordsource, you can effectively bind your existing controls to the "new" recordsource.
 
I tried the idea of the query joining the two tables and then basing a new form based on the query and it works to a certian extent - it showed the donor name, address and phone number but when I try to enter any other data on the form, it says "recordset cannot be updated" - how can I change this? I know what the recordset is, but I have no idea how to enable it to update, allowing me to enter data on the form. I'll definitely try the method just posted and see if that works to my advantage, although any more suggestions would be more than welcome!


Thanks a lot!

Bachfile
 
If the query itself is updateable, there is some property setting on the form that is causing the problem. If the query is not updateable, post the SQL so we can look at it to determine the problem.
 
Thanks for the suggestion, Pat. I will look at that. I remember already looking in the properties for the query and the recordset was not set at snapshot - it was set for the other one, I can't remember what it exactly was and it didn't do anything. I would go into the form and I wouldn't be able to modify any of the fields once they were displayed for a certain record. Anyways, I was also wondering what settings on the form would cause this problem?! If you could provide some suggestions for that, that would be great! Thanks again!

Bachfile
 
I will look for those, Pat. Forgive me for my lack of Access knowledge, but how would I get to these options on the form or are they commands that I would have to use in the Visual Basic code area? Thanks a lot for your help! I really appreciate it!


Bachfile
 

Users who are viewing this thread

Back
Top Bottom