Dynamic Query creation

  • Thread starter Thread starter George Papadopoulos
  • Start date Start date
G

George Papadopoulos

Hello everybody

I am trying to create a new query programmatically. Specifically I have
two forms. On the first one there exists a button which when pressed passes
a code as a parameter and then opens a second form. The code is as shown
below :

Private Sub List_spares_Click()
On Error GoTo Err_cmdGoHere_Click

Dim stDocName As String

stDocName = "LISTA_ANTALLAKTIKWN"
DoCmd.OpenForm stDocName, OpenArgs:=Me.[Kwdikos_episkeyhs]

Exit_cmdGoHere_Click:
Exit Sub

Err_cmdGoHere_Click:
MsgBox Err.Description
Resume Exit_cmdGoHere_Click

End Sub

The OnLoad handler for the second form is


Private Sub Form_Load()

Dim dbEPEMBATHS As Database
Dim rsSpares As Recordset
Dim Kwdikos As Long

If Not IsNull(Me.OpenArgs) Then
Kwdikos = Me.OpenArgs
End If

' Set dbLib to the current database
Set dbEPEMBATHS = CurrentDb

' Create a query
strselect = "SELECT * FROM ANTALLAKTIKA WHERE 'Kwdikos_episkeyhs =
Kwdikos'"

Set rsSpares = dbEPEMBATHS.OpenRecordSet(strselect)

End Sub

The code fails at the line 'Set rsSpares =
dbEPEMBATHS.OpenRecordSet(strselect)' with the error 'Type mismatch'. Where
could there me a type mismatch there?

Thanks in advance

George Papadopoulos
 
Hi George:

I hate to use the '" , "", "' etc. Try this:

Change:
strselect = "SELECT * FROM ANTALLAKTIKA WHERE 'Kwdikos_episkeyhs =
Kwdikos'"

To:
strselect = "Select ANTALLAKTIKA.* FROM ANTALLAKTIKA WHERE Kwdikos_episkeyhs
= " & chr(34) & "Kwdikos" & chr(34) & ";"

Let me know if it works out. BTW, where did you get these names? (G)

Regards,
Al
 
thx Al.

Unfortunately the code does not seem to work properly. By the way the
names are in Greek written using the Latin alphabet. The problem is not in
the syntax, I think though. I changed the code to

strselect = "SELECT * FROM ANTALLAKTIKA" and still get that mismatch.


Ï "Al Borges said:
Hi George:

I hate to use the '" , "", "' etc. Try this:

Change:
strselect = "SELECT * FROM ANTALLAKTIKA WHERE 'Kwdikos_episkeyhs =
Kwdikos'"

To:
strselect = "Select ANTALLAKTIKA.* FROM ANTALLAKTIKA WHERE Kwdikos_episkeyhs
= " & chr(34) & "Kwdikos" & chr(34) & ";"

Let me know if it works out. BTW, where did you get these names? (G)

Regards,
Al

George Papadopoulos said:
Hello everybody

I am trying to create a new query programmatically. Specifically I have
two forms. On the first one there exists a button which when pressed passes
a code as a parameter and then opens a second form. The code is as shown
below :

Private Sub List_spares_Click()
On Error GoTo Err_cmdGoHere_Click

Dim stDocName As String

stDocName = "LISTA_ANTALLAKTIKWN"
DoCmd.OpenForm stDocName, OpenArgs:=Me.[Kwdikos_episkeyhs]

Exit_cmdGoHere_Click:
Exit Sub

Err_cmdGoHere_Click:
MsgBox Err.Description
Resume Exit_cmdGoHere_Click

End Sub

The OnLoad handler for the second form is


Private Sub Form_Load()

Dim dbEPEMBATHS As Database
Dim rsSpares As Recordset
Dim Kwdikos As Long

If Not IsNull(Me.OpenArgs) Then
Kwdikos = Me.OpenArgs
End If

' Set dbLib to the current database
Set dbEPEMBATHS = CurrentDb

' Create a query
strselect = "SELECT * FROM ANTALLAKTIKA WHERE 'Kwdikos_episkeyhs =
Kwdikos'"

Set rsSpares = dbEPEMBATHS.OpenRecordSet(strselect)

End Sub

The code fails at the line 'Set rsSpares =
dbEPEMBATHS.OpenRecordSet(strselect)' with the error 'Type mismatch'. Where
could there me a type mismatch there?

Thanks in advance

George Papadopoulos
Click to expand...
Click to expand...
 
Hi George:

A couple of thoughts-

1) You didn't declare "strselect" as a string... that should give you an
error.
2) You might try to spell out the default dbOpenDynaset, but I think #1 is
the problem...
Change:
Set rsSpares = dbEPEMBATHS.OpenRecordSet(strselect)
To:
Set rsSpares = dbEPEMBATHS.OpenRecordSet(strselect, dbOpenDynaset)

Regards,
Al
 
Back
Top