Error with code for button

[jon]

Hi
I have a form with a subform which is populated from a query.
When I press the button the code below runs
The problem is when the form is opened and the button pressed I get the
error "can't find 'count-baan' referred to in your expression "
but if I open up the form in design view then go back to form view all works
ok.

Can someone please advise where my error is.
the code for the button and the SQL for the query is below.

Thanks

Jon

Private Sub Cmd_openBaan_Click()
On Error GoTo Err_Cmd_openBaan_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CmmBaanEntry"
stLinkCriteria = "[Count-baan]=" & [Fr-Cmm_DueNow subform]![Count-baan]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Cmd_openBaan_Click:
Exit Sub

Err_Cmd_openBaan_Click:
MsgBox Err.Description
Resume Exit_Cmd_openBaan_Click


SELECT Baan.OrderNo, Baan.Job, Componets.[Componet short Name],
Baan.dateIcyBaan, Baan.CmmDate, Componets.Frame, Componets.inspected,
Baan.DueDate, Baan.[Count-baan]
FROM Componets INNER JOIN (ComponentNos INNER JOIN Baan ON
ComponentNos.[Componet No] = Baan.Job) ON Componets.RefNo =
ComponentNos.RefNo
WHERE (((Baan.dateIcyBaan) Is Not Null) AND ((Baan.CmmDate) Is Null) AND
((Componets.inspected)=True))
ORDER BY Baan.DueDate;


End Sub

 

[Allen Browne]

Suggestions.

1. You can't put a SQL statement inside a procedure like that (before the
End Sub.)

2. Include the .Form bit when referring to the subform. More info:
http://allenbrowne.com/casu-04.html

3. Test if Count-baan might be null, in which case the stLinkCriteria would
be incomplete (and so invalid.)

4. If Count-baan is Text type (not Number type), you need extra quotes.

5. If the form is filtered such that it returns no records, and no new
record can be added, Access gets lost when you try to refer to the
non-existent controls in the detail section, or even to controls you can see
in the form header/footer sections. More info:
http://allenbrowne.com/casu-20.html

6. If that doesn't solve it, turn off Name AutoCorrect.
In Access 2000, 2002, or 2003, uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

7. Compact the database:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

Finally:

Private Sub Cmd_openBaan_Click()
On Error GoTo Err_Cmd_openBaan_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CmmBaanEntry"

With Me.[Fr-Cmm_DueNow subform].Form![Count-baan]
If IsNull(.Value) Then
Else
stLinkCriteria = "[Count-baan]=" & .Value
DoCmd.OpenForm stDocName, WhereCondition:=stLinkCriteria
End If
End With

Exit_Cmd_openBaan_Click:
Exit Sub

Err_Cmd_openBaan_Click:
MsgBox Err.Description
Resume Exit_Cmd_openBaan_Click
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi
I have a form with a subform which is populated from a query.
When I press the button the code below runs
The problem is when the form is opened and the button pressed I get the
error "can't find 'count-baan' referred to in your expression "
but if I open up the form in design view then go back to form view all
works ok.

Can someone please advise where my error is.
the code for the button and the SQL for the query is below.

Thanks

Jon

Private Sub Cmd_openBaan_Click()
On Error GoTo Err_Cmd_openBaan_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CmmBaanEntry"
stLinkCriteria = "[Count-baan]=" & [Fr-Cmm_DueNow subform]![Count-baan]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Cmd_openBaan_Click:
Exit Sub

Err_Cmd_openBaan_Click:
MsgBox Err.Description
Resume Exit_Cmd_openBaan_Click


SELECT Baan.OrderNo, Baan.Job, Componets.[Componet short Name],
Baan.dateIcyBaan, Baan.CmmDate, Componets.Frame, Componets.inspected,
Baan.DueDate, Baan.[Count-baan]
FROM Componets INNER JOIN (ComponentNos INNER JOIN Baan ON
ComponentNos.[Componet No] = Baan.Job) ON Componets.RefNo =
ComponentNos.RefNo
WHERE (((Baan.dateIcyBaan) Is Not Null) AND ((Baan.CmmDate) Is Null) AND
((Componets.inspected)=True))
ORDER BY Baan.DueDate;


End Sub

 

[Barry Andrew Hall]

Hi, try

stLinkCriteria = "[Count-baan]= '" & [Fr-Cmm_DueNow subform]![Count-baan] &
"'"

Please copy and paste the line so that you get the apostrophies.
Barry

 

[jon]

Sorted
Thank you

in point 1 sorry I pasted the code in the message wrong

point 2 think was the fix

Point 3 and 4 Count-baan is a indexed field with a auto number entered

once again thanks

Suggestions.

1. You can't put a SQL statement inside a procedure like that (before the
End Sub.)

2. Include the .Form bit when referring to the subform. More info:
http://allenbrowne.com/casu-04.html

3. Test if Count-baan might be null, in which case the stLinkCriteria
would be incomplete (and so invalid.)

4. If Count-baan is Text type (not Number type), you need extra quotes.

5. If the form is filtered such that it returns no records, and no new
record can be added, Access gets lost when you try to refer to the
non-existent controls in the detail section, or even to controls you can
see in the form header/footer sections. More info:
http://allenbrowne.com/casu-20.html

6. If that doesn't solve it, turn off Name AutoCorrect.
In Access 2000, 2002, or 2003, uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

7. Compact the database:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

Finally:

Private Sub Cmd_openBaan_Click()
On Error GoTo Err_Cmd_openBaan_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CmmBaanEntry"

With Me.[Fr-Cmm_DueNow subform].Form![Count-baan]
If IsNull(.Value) Then
Else
stLinkCriteria = "[Count-baan]=" & .Value
DoCmd.OpenForm stDocName, WhereCondition:=stLinkCriteria
End If
End With

Exit_Cmd_openBaan_Click:
Exit Sub

Err_Cmd_openBaan_Click:
MsgBox Err.Description
Resume Exit_Cmd_openBaan_Click
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi
I have a form with a subform which is populated from a query.
When I press the button the code below runs
The problem is when the form is opened and the button pressed I get the
error "can't find 'count-baan' referred to in your expression "
but if I open up the form in design view then go back to form view all
works ok.

Can someone please advise where my error is.
the code for the button and the SQL for the query is below.

Thanks

Jon

Private Sub Cmd_openBaan_Click()
On Error GoTo Err_Cmd_openBaan_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CmmBaanEntry"
stLinkCriteria = "[Count-baan]=" & [Fr-Cmm_DueNow
subform]![Count-baan]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Cmd_openBaan_Click:
Exit Sub

Err_Cmd_openBaan_Click:
MsgBox Err.Description
Resume Exit_Cmd_openBaan_Click


SELECT Baan.OrderNo, Baan.Job, Componets.[Componet short Name],
Baan.dateIcyBaan, Baan.CmmDate, Componets.Frame, Componets.inspected,
Baan.DueDate, Baan.[Count-baan]
FROM Componets INNER JOIN (ComponentNos INNER JOIN Baan ON
ComponentNos.[Componet No] = Baan.Job) ON Componets.RefNo =
ComponentNos.RefNo
WHERE (((Baan.dateIcyBaan) Is Not Null) AND ((Baan.CmmDate) Is Null) AND
((Componets.inspected)=True))
ORDER BY Baan.DueDate;


End Sub

 

[Allen Browne]

Excellent: all sorted.

(Note that even a primary key field can be null - at a new record.)