How can I link tow tables without using a subform?

I have a data entry form for the main table.
On it, I have a combobox with several choices. If the user selects a specific choice, another Form will open, with a seperate table as the recordsource. Here the user enters that table's information, and then it closes.

What I want, is for the two tables to be linked, like I set up in my relationships (One to Many). However, I cannot seem to find a way to link them using the Forms. It always says it cannot find a related record. I have tried to use VBA, saying me.thisfield = the_other_form.that field, when the second form closes, but that doesn't work. I have tried re-setting the recordsource to the main data entry form to include both table's but that doesn't work.

It it possible to use Data Entry and link two table's data without using a SubForm?

Any ideas?

thanks

Are you trying just to add new data to the other table? Or do you also want to navigate the other table but just the related data from the first table?

Richard

All I want to do is add a few data the the other table, and then have that new record in the other table get the autogenerated number of the record from that main table as one of it's fields (a foreign key).

So, only a subset of the records in the main table will have a link to the second table. The process that I am describing, and that we are having a conversation about right now, is when one of those main records DOES have a link to a record in the second table. The pop-up form from the main data entry form accesses the second table, for the user to fill the fields in. Then it closes, and both tables should be linked.



Edited by: SteeleHawk on Mon Nov 24 13:56:41 EST 2008.

How do I "grab" the autogenerated number for the new record on the main table, if it is not referenced by a control on the main data entry form?

I think I see what you are trying to do. If the tables related to the popup forms have data that is related to data on the main form then you can use the code example below to open that popup form to filter the data so that only the related data matching the popup form and the main form are displayed.



However, if the popup does not contain match data you will get the error you mentioned in your first post. If you're trying to enter NEW data ONLY through the popup then using an unbound form with VAB to add NEW data to the linked table is the best route.

If you are trying to do both, add new data and/or update existing data to link table, then the code above will work but you will have to add further code to handle the error when an exist linked record does not exist. You will need to have the form move to allow you to add a new record.

This again can be done whit a simple code struct but before we go down that path let me know if I am understanding correctly what you want first. Without seeing the database its sometimes hard to understand fully what someone realy wants

Richard

Bingo!

That is what I am trying to do.

----

In the interest of TIME, however, right now I am working on using a Subform to add the new data, and I can make it default to invisible, then when the specific value is pulled up from the combobox, it becomes visible, I add the data, but get an error when I try to make it invisible from a button on the subform: "Can't change visibility when this form has the focus."

So, I believe the first solution is the more elegant one, but the second solution (the subform) is already writing the proper data to the tables, so all I have left to do with that is figure out how to make the subform invisible when I am done entering the data on the subform.


Thanks a bunch!

I got the subform to work properly. But it is a stop-gap solution with no scalability. If other tables are added in the future that need to be linked to in a similar fashion (which I am sure they will be), then I will need to use another method, else I will have half-a-dozen invisible subforms sitting on top of my main data entry form.

You can use VBA to make the subform visible and invisible base on selections chosen on the main form very easily. Your other option is to use a tab control to house your subforms.

Richard

Yeah, I got it (subform) working all fine now.

I just wish I could use the other method, for the reasons given in post 82.