Two forms, two tables, same data?

[Rosannafe]

I see that the question has been asked a million times but I still can't get it. Basically I have two different forms for two separate tables. However, both tables have 3 values that are the same, Firstname, LastName, Social. I am trying to create a one to many relationship by using the two tables...the first table for client info and the second table for the various visits for the client. What I'm trying to do now is fairly simple. I click on a command button and the second, visit form, comes up. All I want is for the 3 values to pop up on that second form, visit form. I tried an append but thats not what I wish it to do. Just copy over the values (table to table, form to form). Any suggestions? Another question, I have a search box based on a macro, but I don't know what would be the conditions to create a msgbox that would say the record does not exist, say, if the txt box was ID and the table value was SSN, and the Form txt box was also SSN. Please help! Thanks

 

[Gazer44]

Do you want the visit form to bring up data from the visit table which is linked to what was entered in the client form ??

Can you explain better. (why have same data in 2 different tables ?)

Also-please explain the search macro problem better.
cheers

 

[Rosannafe]

Ok, let me try again. I have two tables, one is for the permanent information of the client, name address etc. I have a second table, for information when the client visits, such as who their doctor was, their ailment etc. Both tables have the same three values, first name, last name, social. All I want is to be able to click on a button on the first form (client info) and have the first, last, and social appear in the second form (visit info) and second table. As for the macro, basically, I need a msg box to pop up when the value I searched for did not come up.

 

[Gazer44]

You can do this a few different ways.

You should have 2 tables, Client Table with an autoNumber field called ClientRef, and a Visits Table with ClientRef and other fields.
Join these on a 1 to many basis

Clientref in ClientsT (1)
Clientref in VisitsT (many)

create a Query which contains the ClientRef, First & Last names from ClienT & the other fields from VisitsT.
Then setup a button which when clicked opens up the Visit info form which has linked criteria based on ClientRef.

The visit info form could also be a Continuous form which either opens seperately or is a subform.

I've got a simple database which shows these examples.
Post your email address & I'll send it.
cheers,
Gary

 

[Gazer44]

p.s.
Re: the SEARCH macro.
It's better to setup a search button via the 'Command Button Wizard' as this does it all for you and gives its own mssg when a search is NOT found.

(this can be changed though- go into the Event Procedure of the button and change the line 'MsgBox Err.Description' to for example -'MsgBox "Search Not Found. Please try again"

If you prefer the macro, then right click on the macro name, select 'Save As/Export', select 'Save As VB Module'. Click OK, then convert, then OK.

Within Modules tab go into Design of the module just created and change the line-MsgBox Error$ to as above.

Then copy and paste the VB code for this module (minus the 1st & last lines) into the Event Procedure of your search button.

Should do it.

 

[Rosannafe]

Thanks for the tips, but I was able to figure out both by myself Just used a little bit of Visual Basic patchwork.

 

[Rosannafe]

Thanks for the tips, but I was able to figure out both by myself Just used a little bit of Visual Basic patchwork.

 

[Gazer44]

OK, GOOD