type mismatch

[PhilipEwen]

hi, i keep getting type mismatch on the following code whilst trying to set a recordset as a query...
is this possible or have i just got the wrong syntax ??

Dim rst As DAO.Recordset '(DAO.Recordset for 2000)
On Error Resume Next
Set rst = "SELECT ([flights].[departairport], [flights].[arriveairport], [flights].[traveldate], [flights].[duration], [flights].[flightname], [flights].[price], [flights].[flightsresortid]) FROM flights WHERE ((([flights].[traveldate]) Between [Forms]![flights]![txt_date] And [Forms]![flights]![txt_dateto]) And (([flights].[flightsresortid])=[flightsresortid]));"

rst.MoveFirst
If Err <> 0 Then lastdate = DateAdd("d", I, StDate): FillTable: Exit Sub 'No records
Do While Not rst.EOF

rst!departairport = Forms!flights!txt_depart
rst!arriveairport = Forms!flights!txt_arrive
rst!traveldate = DateAdd("d", I + 1, StDate)
rst!duration = Forms!flights!txt_duration
rst!flightname = Forms!flights!txt_flightname
rst!price = Forms!flights!txt_price
rst!flightsresortid = Forms!flights!txt_flightsresortid

rst.Update
rst.MoveNext
I = I + 1

Loop

 

[Jack Cowley]

I hate to put up code that I haven't tried so as to save myself some embarrassement when it doesn't work but I will be brave here and suggest something like this:

Dim db As DAO.Database
Dim rst As DAO.Recordset '(DAO.Recordset for 2000)
Set db = CurrentDb
Dim strSQL as String

strSQL = "SELECT ([flights].[departairport], [flights].[arriveairport], [flights].[traveldate], [flights].[duration], [flights].[flightname], [flights].[price], [flights].[flightsresortid]) FROM flights WHERE ((([flights].[traveldate]) Between [Forms]![flights]![txt_date] And [Forms]![flights]![txt_dateto]) And (([flights].[flightsresortid])=[flightsresortid]));"

Set rst = db.OpenRecordset (strSQL, dbOpenDynaset)

On Error Resume Next

rst.MoveFirst
If Err <> 0 Then lastdate = DateAdd("d", I, StDate): FillTable: Exit Sub 'No records
Do While Not rst.EOF
rst.Edit
rst!departairport = Forms!flights!txt_depart
rst!arriveairport = Forms!flights!txt_arrive
rst!traveldate = DateAdd("d", I + 1, StDate)
rst!duration = Forms!flights!txt_duration
rst!flightname = Forms!flights!txt_flightname
rst!price = Forms!flights!txt_price
rst!flightsresortid = Forms!flights!txt_flightsresortid

rst.Update
rst.MoveNext
I = I + 1

Loop

Hopefully that is pretty close but close doesn't really count with Access syntax...

 

[PhilipEwen]

Jack - thanks for your input.....BUT

All it seems to do is crash ACCESS 2K

MSACCESS caused an invalid page fault in
module MSJET40.DLL at 0257:1b04117a.
Registers:
EAX=ffffffff CS=0257 EIP=1b04117a EFLGS=00010246
EBX=00000000 SS=025f ESP=0062e628 EBP=0062e6ac
ECX=02dd52fc DS=025f ESI=02dd5454 FS=704f
EDX=02dd4f8c ES=025f EDI=00080000 GS=0000
Bytes at CS:EIP:
0f b6 48 09 49 74 17 49 0f 85 ed 57 09 00 8b 08
Stack dump:
00000000 02dd5454 1b04dd4b 02dd4f8c ffffffff 1b03c6b7 02dd4f8c 02dd5454 00000000 00000000 02dd5278 02dd4f8c 00000001 02dd5454 02dd54cc 02dd5534

HELP !!!!!!!!!!! :0)

 

[Jack Cowley]

Have you checked your References to see if you have 'Microsoft DAO 3.6 Object Library' checked? To check your References go to any code page where you see the Debug menu and select Tools > References.... Also if there is an obvious error the Complier should let you know when you Compile the code.

[This message has been edited by Jack Cowley (edited 10-19-2001).]

 

[SteveA]

Try the following:

Set rst = "SELECT ([flights].[departairport], [flights].[arriveairport], [flights].[traveldate], [flights].[duration], [flights].[flightname], [flights].[price], [flights].[flightsresortid]) FROM flights WHERE ((([flights].[traveldate]) Between #" & [Forms]![flights]![txt_date] & "# And #" & [Forms]![flights]![txt_dateto] & "#) And (([flights].[flightsresortid])=[flightsresortid]));"

HTH
SteveA

 

[PhilipEwen]

Jack - yep DAO 3.6 is installed.
Steve - thanks, that worked fine..

Cheers Guys for your help - really appreciated

Phil.