Having trouble with code

[jon]

Hi
I am having trouble getting this code to run at the on click event

What happens is the query returns a number 0 to 2 and I want to open
different thing for each number

I get the following errors
with the line
intcount = CInt([Qu-countorders2]![Count-O-N])
I get
Can't find the field 'qu-countorders2' referred to in your expression

with
intcount = "[Qu-countorders2]![Count-O-N]"
I get
type mismatch
I have changed the dim from integer to other formats but no difference

The hole code is here:-

Private Sub But_SelONo_Click()
On Error GoTo Err_But_SelONo_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim intcount As Integer



intcount = "[Qu-countorders2]![Count-O-N]"
'intcount = cint([Qu-countorders2]![Count-O-N])

If intcount = 0 Then
MsgBox "Not a valid order No: or no order No entered." & " Please re
enter." ' , vbOKOnly , "Invalid Entery",
ElseIf intcount = 1 Then
stDocName = "ICYBaanEntry"
stLinkCriteria = "[OrderNo]=" & Me![Txt_reqONo]
DoCmd.OpenForm stDocName, , , stLinkCriteria
'DoCmd.Close acForm, "frm-select-order"

ElseIf intcount > 1 Then
stDocName = "Frm-Select-stage"
stLinkCriteria = "[OrderNo]=" & Me![Txt_reqONo]
DoCmd.OpenForm stDocName, , , stLinkCriteria
' DoCmd.Close acForm, "frm-select-order"

End If







Exit_But_SelONo_Click:
Exit Sub

Err_But_SelONo_Click:
MsgBox Err.Description
Resume Exit_But_SelONo_Click

End Sub


Thanks

Jon

 

[Douglas J. Steele]

If I'm correct in interpretting that [Qu-countorders2] is the name of a
query, and [Count-O-N] is a field in that query, you can't refer to queries
like that.

Treat the query the same as a table, and use DLookup:

intCount = DLookup("[Count-O-N]", "[Qu-countorders2]")

 

[jon]

Sorted

Thanks

Jon

If I'm correct in interpretting that [Qu-countorders2] is the name of a
query, and [Count-O-N] is a field in that query, you can't refer to
queries like that.

Treat the query the same as a table, and use DLookup:

intCount = DLookup("[Count-O-N]", "[Qu-countorders2]")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi
I am having trouble getting this code to run at the on click event

What happens is the query returns a number 0 to 2 and I want to open
different thing for each number

I get the following errors
with the line
intcount = CInt([Qu-countorders2]![Count-O-N])
I get
Can't find the field 'qu-countorders2' referred to in your expression

with
intcount = "[Qu-countorders2]![Count-O-N]"
I get
type mismatch
I have changed the dim from integer to other formats but no difference

The hole code is here:-

Private Sub But_SelONo_Click()
On Error GoTo Err_But_SelONo_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim intcount As Integer



intcount = "[Qu-countorders2]![Count-O-N]"
'intcount = cint([Qu-countorders2]![Count-O-N])

If intcount = 0 Then
MsgBox "Not a valid order No: or no order No entered." & " Please
re enter." ' , vbOKOnly , "Invalid Entery",
ElseIf intcount = 1 Then
stDocName = "ICYBaanEntry"
stLinkCriteria = "[OrderNo]=" & Me![Txt_reqONo]
DoCmd.OpenForm stDocName, , , stLinkCriteria
'DoCmd.Close acForm, "frm-select-order"

ElseIf intcount > 1 Then
stDocName = "Frm-Select-stage"
stLinkCriteria = "[OrderNo]=" & Me![Txt_reqONo]
DoCmd.OpenForm stDocName, , , stLinkCriteria
' DoCmd.Close acForm, "frm-select-order"

End If







Exit_But_SelONo_Click:
Exit Sub

Err_But_SelONo_Click:
MsgBox Err.Description
Resume Exit_But_SelONo_Click

End Sub


Thanks

Jon