stLinkCriteria and subforms

[jcstanley]

Hi

I have a form(Enquiry) which contains a subform(site) which are locked to prevent data being changed.

When a button is clicked I would like to open the currently viewed customer's details and site details in another form called: EDITEnquiry which is not locked.

I have managed to do this using one field(CustomerID) but am having trouble using two fields. - One being on the subform.

The code bellow provides me with the following error message:
Syntax error (missing operator) in query expression '[CustomerID]=2 And [Address] = 123 Site Road'.

Private Sub Edit_Click()
On Error GoTo Err_Edit_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "EDITEnquiry"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID] & " And [Address] = " & Me!EnquirySiteSubform![Address]

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Edit_Click:
Exit Sub

Err_Edit_Click:
MsgBox Err.Description
Resume Exit_Edit_Click

End Sub

Thanks in advance

 

[pbaldy]

Since the second value is text, it would need to be surrounded by single quotes.

 

[jcstanley]

Would it possible for you to show me where the quotes need to go as i am quite new to this.

Thanks

 

[pbaldy]

Try:

stLinkCriteria = "[CustomerID]=" & Me![CustomerID] & " And [Address] = '" & Me!EnquirySiteSubform![Address] & "'"

 

[jcstanley]

Now instead of an error I get a Enter Parameter Value box asking for the address. If you type in the address it just goes to the first record in the site but it does choose the correct customerID.

 

[pbaldy]

That doesn't make sense, if it was pulling the address off the form correctly before. That part didn't change. Can you post the exact code now? And confirm nothing else changed? That parameter box implies it can't find the value.

 

[jcstanley]

I have only changed the line stLinkCritera... etc. I replaced it with your code as follows:

Private Sub Edit_Click()
On Error GoTo Err_Edit_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "EDITEnquiry"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID] & " And [Address] = '" & Me!EnquirySiteSubform![Address] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Edit_Click:
Exit Sub

Err_Edit_Click:
MsgBox Err.Description
Resume Exit_Edit_Click

End Sub

One thing that I did notice is that when you halt the code in the debugger at the line DoCmd.OpenForm........ and hover over the stLinkCriteria line the values are correct!

 

[pbaldy]

What happens if you open that form directly? It sounds like something in that form may be looking for a value.

 

[jcstanley]

When opened directly it is fine. I can browse through the records and no boxes appear.

 

[pbaldy]

Can you post a sample db?

 

[boblarson]

I believe it's a subform syntax problem again -
try changing

stLinkCriteria = "[CustomerID]=" & Me![CustomerID] & " And [Address] = '" & Me!EnquirySiteSubform![Address] & "'"

to

stLinkCriteria = "[CustomerID]=" & Me![CustomerID] & " And [Address] = '" & Forms!YourMainFormNameHere.YourSubformContainerNameHere.Form.YourControlThatHousesAddressHere & "'"

You should make sure your text box that houses the field Address is named differently (example txtAddress).

So, with your form named "Enquiry" and Subform named "site" and a text box named txtAddress the syntax SHOULD be (if everything else is right):

stLinkCriteria = "[CustomerID]=" & Me.CustomerID & " And [Address] = '" & Forms!Enquiry.Site.Form.txtAddress & "'"

Also, Me.CustomerID will only be valid if the code you are placing this in is in the form that you are referring to. If CustomerID is being retreived from the subform too, then you would need to change it to
Forms!Enquiry.Site.Form.CustomerID

 

[jcstanley]

Hi
I have now changed it to

stLinkCriteria = "[CustomerID]=" & Me![CustomerID] & " And [Address] = '" & Forms!Enquiry.Site.Form.txtAddress & "'"

but still get the parameter value box for the address.

The CustomerID is on the main form - (Enquiry).

sooooo frustrating!!!

thanks for your suggestions

 

[boblarson]

Can you post a stripped down version of your db here? It might help for us to look at it.

 

[ansentry]

Maybe I am missing something but if I am reading your post correctly then you have a main form with a subform (all controls locked)

You want to be able to click on a button on the locked Main form/Subform and open a copy of the Mainform / Subform that is not locked, to the current record.

If I am correct then all you need for the criteria is to link the Primary key from the locked Main form to the primary key of the unlocked Main form and the subform will take care of itself.


e.g. stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

I have tested my theory on a sample db of mine and it works fine.

PS Why don't you just have a button on your current form that when clicked asks for a password and if the password is correct is unlocks all to controls on both the main and subform.

Just my 2 pence worth, it's late here so maybe I am of the mark.

 

[jcstanley]

ok guys here is a sample version of my database

thanks

 

[jkl0]

CustomerID is a unique field so you don't need to include the Address to identify it.

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

Should work.

Hope this Helps?

 

[shahzadmasih]

Hi, This post is very informative, however I would like some specific information. If someone can help me then please send me a private message. Best Regards,

 

[jcstanley]

CustomerID is a unique field so you don't need to include the Address to identify it.

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]

Should work.

Hope this Helps?

This does work in the sense that it opens the edit enquiry form for the specified customer but not for the sites.

For example:
If you are viewing CustomerID (2) and are viewing the second record in the site subform which would be (My Site 2), when the the EDIT button is clicked the EDIT Enquiry form is opened to display CustomerID (2) but NOT the second record in the site subform. Instead it shows the first record which is (123 Site Road)

 

[jkl0]

Try this under your edit button.

Private Sub Edit_Click()
On Error GoTo Err_Edit_Click

    Dim stDocName As String
    Dim stLinkCriteria, stFilter As String

    stDocName = "EDIT Enquiry"
    
    stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
    stFilter = "[Address] = " & "'" & Me.Site.Form.txtAddress & "'"

    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Forms![EDIT Enquiry].[EnquirySiteSubform].Form.Filter = stFilter
    Forms![EDIT Enquiry].[EnquirySiteSubform].Form.FilterOn = True

Exit_Edit_Click:
    Exit Sub

Err_Edit_Click:
    MsgBox Err.Description
    Resume Exit_Edit_Click
    
End Sub

I had to create a seperate filter for the subform, but I think this should work.

 

[jcstanley]

Thanks for that. It works perfectly!!!!!!

Thanks for all your suggestions