data type mismatch at query, that should supply data to form

[ReginaF]

Hello,
I would like to ask for the kind help of the community, to figure out the problem I am stuck at.
I would like to run a query with a button click. Said button is on a form, from where I would like to access a new form. The second form would get the data form the formerly mentioned query. The query is quite simple, it contains all records from two joined tables.
This is the code for the first form, where the button is:

Private Sub editSubBtn_Click()
Dim prevCtrl As Control
Set prevCtrl = Screen.PreviousControl
If prevCtrl.Name <> "frm Sub Szövet" Then
Exit Sub
End If

Dim tempReceiveDate As TempVar
TempVars!tempReceiveDate = Me.Átvétel_dátuma.Value

DoCmd.OpenForm "frm Szovet Edit", OpenArgs:=subNo 'subNo is defined as: Public subNo As Long, at the very top of my code

End Sub

On the second form:

Private Sub Form_Open(Cancel As Integer)
Me.AllowEdits = True
Me.AllowAdditions = False
If Nz(Me.OpenArgs) = 0 Then
Me.RecordSource = "qry_SzovetTarolas"
Else
Me.RecordSource = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE ((tblSzovet.[Azonosító])= 'Me.OpenArgs' );"
End If
End Sub

The bold part is where I get most of my error messages... Usually it is data type mismatch, as tblSzovet.[Azonosító] is the PK, and AutoNumber and the OpenArgs I want to pass should also be an integer. However, if I leave the ' ' then a pop-up window appears at the end of running, asking for a value to Me.OpenArgs. Even if I give it a number then the whole system stops, like it stuck in a loop.

I know this might be a very basic problem, but I would appreciate any help in solving it.
Thanks in advance,
Regina

 

[MajP]

if Azonosító is a text field
"SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító]= '" & Me.OpenArgs & "'"
or
if Azonosító is a numeric field
"SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító]= " & Me.OpenArgs

 

[Josef P.]

If [Azonosító] is a str/text data type:
Me.RecordSource = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító] = '" & Me.OpenArgs & "'"
If [Azonosító] is a numeric (long) data type:
Me.RecordSource = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító] = " & Me.OpenArgs

Alternative:
Fixed RecordSource: qry_SzovetTarolas
+ open form with WhereCondition parameter:
DoCmd.OpenForm "frm Szovet Edit", , , "[Azonosító] = " & subNo

 

[Pat Hartman]

Also, do NOT use screen.Previous control this way. It is very dangerous. What if the user has clicked some other control before clicking the button. Just reference the actual control name.

 

[ReginaF]

if Azonosító is a text field
"SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE ((tblSzovet.[Azonosító])= '" & Me.OpenArgs & "'"
or
if Azonosító is a numeric field
"SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE ((tblSzovet.[Azonosító])= " & Me.OpenArgs

Thanks for the help, I do not want to abuse your kindness, but I would like to ask for a bit more help... I have tried your solution (the second version, as I want to pass an integer) and it work fabulously! However, out of the blue, it stopped working again... I can see that it tries to open the second form, it goes into the Else branch of the If function, it also passes the correct value to Me.OpenArgs, but after that the whole program freezes. My best guess is, that something goes sideways with my query. Do you have any idea what can cause this and how can I fix it?

 

[Pat Hartman]

Have you tried the query outside of the code?

 

[MajP]

As Pat said try

Private Sub Form_Open(Cancel As Integer)
  Me.AllowEdits = True
  Me.AllowAdditions = False
  If Nz(Me.OpenArgs) = 0 Then
    Me.RecordSource = "qry_SzovetTarolas"
  Else
  dim source as string
  Source = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE ((tblSzovet.[Azonosító])= '" & Me.OpenArgs & "'" )
  debug.print source
  'Paste debug into the query builder. Go to SQL View'
End If
End Sub

 

[ReginaF]

Have you tried the query outside of the code?

If I just run the query on its own, it works just fine. This is the SQL View code for it:

SELECT tblSzovet.*, tblTarolasiNaplo.*
FROM tblSzovet LEFT JOIN tblTarolasiNaplo ON tblSzovet.Azonosító = tblTarolasiNaplo.[Szovet ID];

The strange thing is, how the suggested solution worked just fine, and then out of the blue it went back to not working....

 

[MajP]

That is not what we are asking. We are asking if you resolved the query string and tried to run that?

Source = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE ((tblSzovet.[Azonosító])= '" & Me.OpenArgs & "'" )
 debug.print source
 'Paste debug into the query builder. Go to SQL View'

 

[ReginaF]

That is not what we are asking. We are asking if you resolved the query string and tried to run that?

Source = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE ((tblSzovet.[Azonosító])= '" & Me.OpenArgs & "'" )
debug.print source
'Paste debug into the query builder. Go to SQL View'

Sorry, I misunderstood. I tried to copy your code but I get a compile error "Expected: end of statement".

 

[MajP]

That parentheses is on the outside and not needed. Try
Source = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító])= '" & Me.OpenArgs & "'"

 

[ReginaF]

That parentheses is on the outside and not needed. Try
Source = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító])= '" & Me.OpenArgs & "'"

I ran the code, however, I got no error message or anything like that. The only thing that happened is that the window froze again.

 

[MajP]

I ran the code, however, I got no error message or anything like that. The only thing that happened is that the window froze again.

We are asking you to run the code that will print the SQL string to the immediate window. Then grab that string and go to query editor in SQL view, paste the string of sql code and run it. Does it run? If not what is the error? Then come back and show us the SQL string too.

 

[ReginaF]

We are asking you to run the code that will print the SQL string to the immediate window. Then grab that string and go to query editor in SQL view, paste the string of sql code and run it. Does it run? If not what is the error? Then come back and show us the SQL string too.

Sorry again for the comfusion, I expected the immediate window to be more visible, I missed it before. The SQL string in that came back is:

SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító])= '1395'

When I ran it the first error was "extra ) in query expression". After I took that out I got "Data type mismatch in criteria expression".

 

[MajP]

That looks to me like
tblSzovet.[Azonosító]
stores real numbers not text.
What happens if you get rid of the ' ?
Source = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító] = " & Me.OpenArgs

 

[ReginaF]

That looks to me like
tblSzovet.[Azonosító]
stores real numbers not text.
What happens if you get rid of the ' ?
Source = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító] = " & Me.OpenArgs

You are right, it stores numbers, it is defined as AutoNumber in the table, it is the primary key. What puzzles me, is that what you wrote here is basically the same as the first solution you provided earlier this week, and when I copied that into my code it worked fine for a few hours, and then out of the blue it started to freeze out access again.
Now when I got rid of the ' the same happens, the form that should open fails to do so, and the window freezes out just the same. But when I copied the str back form the immediate window to the qry, it ran just fine on its own, and gave back the record it supposed to.

 

[MajP]

Maybe what you are passing in is not a viable value. Try verifying it is a number and then casting it to a long.

Private Sub Form_Open(Cancel As Integer)
  dim arg as long
  Me.AllowEdits = True
  Me.AllowAdditions = False
  If (Me.OpenArgs & "") = "" Then
     Me.RecordSource = "qry_SzovetTarolas"
  Else
     debug.print me.openargs ' verify the value
     if isnumeric(me.openargs) then
        arg = clng(me.openargs)
        Me.RecordSource = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító] = " & arg  
      End If
   end if
End Sub

 

[ReginaF]

Maybe what you are passing in is not a viable value. Try verifying it is a number and then casting it to a long.

Private Sub Form_Open(Cancel As Integer)
  dim arg as long
  Me.AllowEdits = True
  Me.AllowAdditions = False
  If (Me.OpenArgs & "") = "" Then
     Me.RecordSource = "qry_SzovetTarolas"
  Else
     debug.print me.openargs ' verify the value
     if isnumeric(me.openargs) then
        arg = clng(me.openargs)
        Me.RecordSource = "SELECT qry_SzovetTarolas.* FROM qry_SzovetTarolas WHERE tblSzovet.[Azonosító] = " & arg 
      End If
   end if
End Sub

Still freezing out. The immediate window gave me back: 1395. This is what the number supposed to be, and I guess this is an integer (?).

 

[Gasman]

No, a long can be 1395.

 

[MajP]

I guess this is an integer (?).

I do not know, you tell me. Go to table design view and tell me the datatype of tblSzovet.[Azonosító]
It could be text, double, long etc. I just can tell what it looks like, you can tell me what it is.