Link to Report from Form (1 Viewer)

[deagio]

Hi,

I have 2 forms and both have one common field. I am trying to have a button on one of the forms which opens up the other form with information relevent to the record i am currently viewing, as im runing Access with SQL tables, the wizard doesnt seem to work.

Here is the code i have at the moment:


Private Sub Command141_Click()
On Error GoTo Err_Command141_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "co4"

stLinkCriteria = "[Delivery_SigRef]=" & Me![Delivery_SigRef]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command141_Click:
Exit Sub

Err_Command141_Click:
MsgBox Err.Description
Resume Exit_Command141_Click

End Sub


I get the error 'Invalid Column Name 'Fieldcontents''

Does any one have any ideas how i could make this button work with my forms?


Thanks!!

James

 

[Pat Hartman]

The code looks fine as long as Delivery_SigRef is numeric. If it is text, it needs to be enclosed in quotes. The error is probably coming from the second form. Look at the sort and filter properties and look for column names that have been changed.

 

[deagio]

Hi

Hi,

Thanks for that, yes the field does have some letters in it, so I added the quote marks, but now I get a different error message, 'Microsoft Access can't find the fiels "Delivery_SigRef" refered to in your expression."

Ive checked and the field is definately there in both forms, i can only think its somthing to do with the database being run over SQL.

Heres a copy of the command now:


Private Sub cmdOne_Click()
On Error GoTo Err_cmdOne_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "co4"

stLinkCriteria = "['Delivery_SigRef']=" & Me!['Delivery_SigRef]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOne_Click:
Exit Sub

Err_cmdOne_Click:
MsgBox Err.Description
Resume Exit_cmdOne_Click

End Sub



Thanks again for your help!


James

 

[Pat Hartman]

You have put the quotes in the wrong place.

stLinkCriteria = "[Delivery_SigRef]= '" & Me.[Delivery_SigRef] & "'"

 

[deagio]

Hi

Hi,

I tried pasting in your line of code with the correct punctuation and there is now no error, but the second form opens blank, not displaying any records.

I've looked through the whole forms code and I cant see anything else major that could be causing this problem.

I dont know if any of the other functions on this form could be causing a problem:


Private Sub Command108_Click()
On Error GoTo Err_Command108_Click

Dim stDocName As String

stDocName = "Paid"
DoCmd.RunMacro stDocName

Exit_Command108_Click:
Exit Sub

Err_Command108_Click:
MsgBox Err.Description
Resume Exit_Command108_Click

End Sub
Private Sub Command110_Click()
On Error GoTo Err_Command110_Click

Dim stDocName As String

stDocName = "Unpaid"
DoCmd.RunMacro stDocName

Exit_Command110_Click:
Exit Sub

Err_Command110_Click:
MsgBox Err.Description
Resume Exit_Command110_Click


End Sub
Private Sub Form_Current()
Dim objIE As SHDocVw.InternetExplorer
Dim strURL As String

BmpWebPath_Sig.SetFocus
strURL = Me.BmpWebPath_Sig.Text

Set objIE = Me.WebBrowser.Object
objIE.Navigate strURL
End Sub
Private Sub Command144_Click()
On Error GoTo Err_Command144_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "co4"

stLinkCriteria = "[Delivery_SigRef]= '" & Me.[Delivery_SigRef] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command144_Click:
Exit Sub

Err_Command144_Click:
MsgBox Err.Description
Resume Exit_Command144_Click

End Sub


Thanks again!


James

 

[Pat Hartman]

Does the form open up properly when you substitute a literal value for the form field reference? It is possible that you have a problem with the form's RecordSource query.

BTW
1. Leaving the generated object names such as command110, command114 is poor practice. Immediately after you create an object and BEFORE you add any event code, rename the control so it has a meaningful name. If you rename them now, the current code will become "disassociated" and you'll have to cut and paste it back into the new event subs and delete the old ones.
2. Get rid of the macros. You can convert them to code and place the code in your subs. If the macros are used in multiple places, the put the converted code in a standard module and call the subs.