Link form

[nigel brown]

Hi

I have a form open, I would like to open another form taken from a different table, they are linked through [Site name].

How do I link it so the associated record opens in the second form?

Regards

Nigel

 

[CBrighton]

You could use the filter arguement in OpenForm :

Replace FormName with the name of the form you are opening, FieldName with the field in the the table bound to the form you are opening ([Site Name] I guess) and ControlName with the control on the current form which is bound to the same field.

DoCmd.OpenForm "FormName", , , "FieldName = " & ControlName

:edit:

"FieldName = " & ControlName is more likely to need to be "FieldName = '" & ControlName & "'" or something similar as I assume you are linking on a text field.

 

[nigel brown]

You could use the filter arguement in OpenForm :

Replace FormName with the name of the form you are opening, FieldName with the field in the the table bound to the form you are opening ([Site Name] I guess) and ControlName with the control on the current form which is bound to the same field.

DoCmd.OpenForm "FormName", , , "FieldName = " & ControlName

:edit:

"FieldName = " & ControlName is more likely to need to be "FieldName = '" & ControlName & "'" or something similar as I assume you are linking on a text field.

Hi

It did not like this

DoCmd.OpenForm "Count of Learners Employers", , , "Site Name = "" & "Site Name&""

Yes, linking one text field from one form and opening another form using that value as reference to pull that value from a query result.



Nigel

 

[DCrake]

Correct syntax

DoCmd.OpenForm "Count of Learners Employers", , , "[Site Name] = '" & Me![Site Name] &"'"

 

[CBrighton]

try this:

DoCmd.OpenForm "Count of Learners Employers", , , "[Site Name] = '" & [Site Name] & "'"

Note that the double quotation marks ( " ) are surrounding the string, single quotes / apostraphies ( ' ) are before and after the control reference.

You may need square brackets around the form name too, not sure. FYI, spaces in field / control / form names are generally a bad idea.

:edit:

David beat me to it

 

[nigel brown]

Correct syntax

DoCmd.OpenForm "Count of Learners Employers", , , "[Site Name] = '" & Me![Site Name] &"'"

I put the code in to a event of a button on the first form, on pressing the button, the form opens but there are no text fields or other button on the form, nearly there.

Nigel

 

[CBrighton]

The textbox control on the first form which is bound to Site Name, is the control itself also called Site Name?

If it's loading the form with no data / controls then it's likely there were no records found. Assuming there should be records found this would prompt me to double check the control & field names in the above code.

 

[nigel brown]

try this:

DoCmd.OpenForm "Count of Learners Employers", , , "[Site Name] = '" & [Site Name] & "'"

Note that the double quotation marks ( " ) are surrounding the string, single quotes / apostraphies ( ' ) are before and after the control reference.

You may need square brackets around the form name too, not sure. FYI, spaces in field / control / form names are generally a bad idea.

:edit:

David beat me to it

Hi again

Tried without and with the square brackets and same result, form with no detail.

I am not VBA or code savvy so I am not much use, sorry.

Nigel

 

[CBrighton]

My code is the same as DCrake's, except he added the me! before the control name.

The code itself is correct, I'm concerned that the second form is finding 0 records which means the criteria in the code is wrong.

"[Site Name] = '" & [Site Name] & "'"

The above is only correct if the textbox on the first form and the field in the table bound to the second form are both called Site Name and the field is a text datatype. Can you confirm this is the case?

 

[nigel brown]

My code is the same as DCrake's, except he added the me! before the control name.

The code itself is correct, I'm concerned that the second form is finding 0 records which means the criteria in the code is wrong.

"[Site Name] = '" & [Site Name] & "'"

The above is only correct if the textbox on the first form and the field in the table bound to the second form are both called Site Name and the field is a text datatype. Can you confirm this is the case?

Both are called Site Name and both are text fields.

 

[nigel brown]

I hve found out why the form is blank.

If there is no associated record, it returns a blank. If there is an associated record, it returns the record and works.

Thanks for all your help.

Nigel