Hello,

I have some experience in access but am having a hard time with the following:

I have two tables that make a 1-many relationship. I would like to have a form with a pop up form. Upon the selection of an option box in the main form, another form would open, record the primary key of the first table as well any the entered data in the second table.

Basically this pop up form would be acting like a subform. I know in a subform you just use the parent and child to link it to a main form but I'm not sure what to do when linking 2 forms.

Any help is appreciated.

"Basically this pop up form would be acting like a subform."

firstly, a popup form is not like a subform; a subform has properties that allow you to synchronize it with the main form, a popup form does not ... just a little correction on your terminology <smile> ... I think what you mean is that the popup form contains records that are related to the records in the main form

When you open the second form, you can specify what records to show using the WHERE clause parameter of the OpenForm action



WHERE
FK_fieldname is the name of the foreign key field in the RecordSource of the second form
PKcontrolname is the Name property of the control on the first form with the Primary Key

... now this will work fine for records that already exist... but when you create records, you need to specify how that field will get filled. There are 2 ways to do this:

1. if the first form will always be open, specify this for the DefaultValue of the foreign key control



2. use code in the BeforeInsert event of the second form to test if the first form is open and, if so, fill the foreign key

Thanks for the welcome and the help!!

I just need a bit of clarification on the FK_fieldname and PKcontrolname.

There are two tables:
tblFlat( FlatID(autonum), Unit)

tblMeasurement(MeasurementID(autonum), V, C, M, Y, Date(now), FlatID)

So upon the selection of an option box on form1, which is recorded in the Unit field, I would like the the second form to open, allow me to enter the 4 measurements(V,C,M,Y) and record the Unit that was selected all in tblMeasurement.

So the FK_fieldname would be FlatID? What about the PKcontrolname?

Thanks again!

ps. i watched your youtube tutorial video on the random selecting names and it was really helpful, I plan add the random selection to this project.

Hi cdc (what is your name?)

to learn more about properties such as ControlName, read this:

Access Basics
http://www.utteraccess.com/forums/showflat...;Number=1595005
free 8-part free tutorial that covers essentials in Access

I am glad you liked my video tutorial on generating random numbers, thanks for commenting!

~~~

The Name property of a control is different than the Controlsource -- this is your field name. To simplify things, if a control is bound, I make the Name of the control the same as the ControlSource -- so PKcontrolname is the Name property of the control on the main form that contains the primary key.

FK_fieldname is the name of the foreign key field in the related table (what you are using to link data -- FlatID -- if a field is the same thing, I name it the same -- so the FKfieldname would be the same as the PKfieldname -- and you have done this, which is good.

PK = primary key
FK = foreign key

Why are you using a popup form instead of a subform?

What do V, C, M, and Y represent? What is the data type of each? What are examples of its values?

~~~

also, do not use Date as a fieldname, it is a reserved word ... watch my tutorial on Planning a Database

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

Hello - it's Craig

Not sure why I am using a popup, I had it working with a subform but it was felt that it would be less confusing for the employees entering the data, who are not at all familiar with access, as a seperate pop up window. The plan is that when a blank measurement is taken, the popup will close. They will then select a new unit, at this time the popup form opens again to a new record.

V, C, M, Y are measurements taken, each is a number between .10 and 1.30.

I'm still iffy on some of the terminology (contolsource, controlname, ect... ) but I'll do some more reading and should be able to get them straight.

ahh about that date as a fieldname, my old professor would not be happy to see that... dumb mistake on my part.

Thanks again!!

Edited by: cdc on Thu Feb 19 14:51:13 EST 2009.

Hi Craig,

you're welcome

in other words, you are taking the focus of the user off the main form so they can focus on entering the new information ... instead, how about accomplishing the same goal with a tab control?

The main information would be above the tab control so it shows no matter which tab is active

Then fields that are important to fill but not important to see when you are on the other form can be on the first tab.

The second tab can have your subform

"V, C, M, Y are measurements taken"

yes, I understood that from your tablename <smile> ... I was asking what specific measurements they are ... it helps to know a bit about the reason for your database ... what are you measuring? what kind of sample are you working with to take the measurements? how are the measurements being done?

Another consideration is precision.

how many decimal places will the number have? If less than 4, I would recommend storing the data using the currency data type for the accuracy it provides. Single and Double precision numbers are stored in floating point (exponential) format, so they are not accurate enough for what you probably need ... they are susceptable to creating "ghost digits" several places out ... making a number look like it has more precision than it does! so even if you enter 1.2, what is returned to an equation might be 1.200005892

"my old professor would not be happy to see that"

... that thinking probably triggered (and will trigger) other things from the class that would be good to recall now ... so you will end up making him proud <smile>

I have not used tab control too often to be honest. I feel that if I can get it working properly the popup form would be easier on the employees.

The measurements are density checks to ensure that the product is up to specifications. This will allow measurements to be taken during manufacturing hours to show any trends between different machines, operators and other conditions, generally over a shift. Specifications are +/- .05 and there is some error in the measurement devices as well. There are close to 20 different machines in use and all checks will be done at the same station. The operator will select the machine from an option box, the window pops up and on a null measurement it closes again.

Any delay is a large problem so I felt this way would keep things rolling smoothly. The operator would only need a mouse as the measuring devices are hooked up to the comupter.

Hi Craig,

I have been out of town ... how are things going?