Expert: Scottgem - 2/16/2005

Question
hi ther im mitch in a rookie at access
i use XP (sp2) and office 2003
i have two questions (hope you can help me some one else did not understand me)

if got two forms (its a mp3 data base)

1 main form , and 1 sub form (pop up)

the a record in the sub form contains extra info about  a record in the main form
example if record 5 is open in the main and i open the sub record 5 should also open

this is posible couse both have a uniek hyper link to the mp3 that is handeld 9this hyper links is in bothe the main as wel as the sub form)

the trick is to open the form

i know if ik make a command button and choise open form , specific data i get a window that let me specify which field in the main form and the sub form are the same
this solves my problem BUT
what i want to do is to open this form by double clicking on a certain texkt box

this can be done by going to properties of that text box in the even tab you can enter action for double click
so far so good
now i chouse macro an chouse open form
i specify wich form to open

but now it just opens the subform instead of presenting the richt record in that sub form

how do i tell it to look for the common data fields ??


question 2

i made to tables made forms from them
in both is a field contaning the same text

what i did was make a append query so that the info of table 1 field 1 is copied to table 2 field 2 (lest say in bith tables are now ten entries)

now i start using one form (from table 1)
what i want is to update table 2 field to with the new data
(table 1 field 1 has now 14 entries  table 2 field 2 still 10 )
i want the 4 new entries coppied to tables 2 field 2 without coppieing the old once that are al ready in there

i wnat to do this with a query becouse i gan put this command under a button on the form

thanks in advance ... mitch  

Answer
1) First, a Subform is actually a form placed on a mainform, not a popup. Just so we are talking the same language. So why not make this as a subform instead of a popup? Place it on the main form and link it on the appropriate key field, then you don't have to worry about double clicking anything. As you navigate records in the main form, the subform reflects the related records.

If you still want to use a popup, there are a few ways to filter the records in the popup. Generally, when you have a related table, its usually a one to many relation. Otherwise there isn't much of a need for a separate table. So you want to apply a filter. I don't use macros, so I'm not sure how to do it in a macro, but the command button wizard will generate the code to open a new form using a filter. I would use the wizard, then copy and paste the code into the double click event of your text box.

2) I don't understand what you are doing here. You should NOT be duplicating data in two tables. That violates normalization rules.

HTH
Scott<>