calling a specific record from a form

[lebanoncedars]

Hi there,
I have 2 forms, form1 and form2, one is different design than the other. I made one for reservation and another for listing all customers so I can see all customers one time.

Question : how can I open specific record in Form1 (if I click Example on lastname) to open this specific record in form2, so I can be able to modify it ?

Thank you
Lebanoncedars

 

[gwinn7]

The quickest approach to take on this is to employ the subform control. If you have setup your table relationship properly between the Customer and the Reservation tables, then you can link the two forms through the Master/Child relationship through the subform.

So, first create a form, then place a subform control on the main form. Then, link the subform to the Form2 and make sure the properties of the subform are set to share the field that establishes the relationship between the Reservation and the Customer. Then, POW, it should be done. Try opening the form and clicking through the customers and watch the reservations open based on the Customer identifier.

Hopefully, that pointed you in the right direction. Take care.

Gary
gwinn7
A+, Network+

 

[lebanoncedars]

Hi gary,
Thanks for the answer.
The two forms are related and have links between each other.
But what I need, whenever I click on LastName field (Example: Smith) the LastName in form1, the second form Reservation opens with all information Mr Smith,

Cos the 2 forms are different design, one to show all customers one time, and second to show one customer at the time.

Thanks again Gary,
Lebanoncedars

 

[lebanoncedars]

Hi gary,
Thanks for the answer.
The two forms are related and have links between each other.
But what I need, whenever I click on LastName field (Example: Smith) the LastName in form1, the second form Reservation opens with all information Mr Smith,

Cos the 2 forms are different design, one to show all customers one time, and second to show one customer at the time.

Thanks again Gary,
Lebanoncedars

 

[danvlas]

Paste this code into a module:

Sub OpenFiltered(FormName, FilterString)
DoCmd.OpenForm FormName, , , FilterString
End Sub

Now, in your continuous form:

Private Sub LastName_Click()
Dim fltrStr As String
If Not IsNull(KeyFieldName) Then
'for a numeric key field
fltrStr = "TableName.KeyFieldName = " & [KeyControlName]
'or for a text key field:
'fltrStr = "TableName.KeyFieldName = '" & [KeyControlName] & "'"
OpenFiltered(Me.Name, fltrStr)
End Sub

KeyFieldName: name of the unique identifier in the table
KeyControlName = name of the control on the form that displays the value of KeyFieldName

You can use the OpenFiltered procedure in any situation where you need to open a form to display a specific record.
If the filter is a zero-length string "", the form will display all records.

HTH,

Dan

 

[lebanoncedars]

Thank you dan,

I will give a try,

thanks anyway
lebanoncedars

 

[lebanoncedars]

Hi Dan,
I created a module. I don't know how the module is going to work alone.
maybe I have to call it from somewhere!!

But when I create the lastName click()
is been an error in the last sentence coding.

OpenFiltered(Me.Name, fltrStr)

Compile error....syntex error =

Please check it out.
and what about that module I create.
I need little details.
Tnx
Lebanoncedars

 

[danvlas]

I would normally ask for details on the error message, but here, I suspect an 'Invalid use of me keyword', am I right? Or is it "can't find the field blah blah blah"?

To paste the second code in the right place:
open the form in Design View
go to View-Code
Paste it there.

Make sure you have the correct field names.

And I found a (big) typo in my post:
Replace
OpenFiltered(Me.Name, fltrStr)
with
OpenFiltered("SingleFormName", fltrStr)

where SingleFormName is the exact name of the form you want to open.

Dan

 

[lebanoncedars]

Hi Dan,
Still the same error, check it out
the fields are right.
the second LastName is the Key controlName in the customers listing form.
I'm using forms by the way.
this is the code still giving Compile error...syntax expected =
where Openfiltered.....

Private Sub LastName_Click()
Dim fltrStr As String
If Not IsNull(LastName) Then
'for a numeric key field
fltrStr = "Customer_Reservation.LastName = " & [LastName]
'or for a text key field:
'fltrStr = "TableName.FieldName = '" & [ControlKeyName] & "'"
OpenFiltered("Customer_reservation", fltrStr)
End Sub

 

[danvlas]

Last name is a text field, so:

Private Sub LastName_Click()
Dim fltrStr As String
If Not IsNull(LastName) Then
fltrStr = "TableName.FieldName = '" & [ControlKeyName] & "'"
OpenFiltered("Customer_reservation", fltrStr)
End Sub

 

[lebanoncedars]

Hi Dan,
Still the same error.

Compile error...syntax expected =

Sorry for trouble
check the previous message coding.
thnx
lebanoncedars

 

[danvlas]

Could you post the exact code you are using???

This is really something...

 

[lebanoncedars]

Hi Dan,
this is the module code


Sub OpenFiltered(FormName, FilterString)
DoCmd.OpenForm FormName, , , FilterString
End Sub

and this is the continuous form code:

Private Sub LastName_Click()
Dim fltrStr As String
If Not IsNull(KeyFieldName) Then
'for a numeric key field
fltrStr = "TableName.KeyFieldName = " & [KeyControlName]
'or for a text key field:
'fltrStr = "TableName.KeyFieldName = '" & [KeyControlName] & "'"
OpenFiltered("SingleFormName", fltrStr)
End Sub

And this is details about the forms:
Form1= (Continous form)
Form2= (Customer Reservation)
KeyControlName= (LastName field) in Continous form1(this is the one I have to click to open Customer reservation form1)
KeyFieldName= (Customer_ID) a unique name or ??? whatever..

Problem,,,is getting error..when I close VB..
Compile Error
Syntax expected =

Thnx
Lebanoncedars