Using recordset I get errors

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I'm trying to do something like the following but keep
getting error messages like type mismatch. What should
this look like?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "DoCmd.OpenQuery 'QueryAv'"
rs![Booking ID] = varNewID
rs.Update
rs.Close
Set rs = Nothing

QueryAV is:

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Day Number],
AVAILABILITY.[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));
 
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from
QueryAv",CurrentProject.Connection,adOpenDynamic,adLockOpti
mistic
rs![Booking ID] = varNewID
rs.Update
rs.Close
Set rs = Nothing

You'd be better off doing a Update Query:

CurrentProject.Connection.Execute "Update queryav set
[Booking ID]=" & varNewID


Chris Nebinger
 
Any ideas? Tried this suggestion but got a Run-time error:
-2147217904(80040e10)
No value given for one or more required parameters

statement was:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from QueryAv",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs![Booking ID] = varNewID
rs.Update
rs.Close
Set rs = Nothing


QueryAv is:

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Day Number],
AVAILABILITY.[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));


-----Original Message-----
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from
QueryAv",CurrentProject.Connection,adOpenDynamic,adLockOp ti
mistic
rs![Booking ID] = varNewID
rs.Update
rs.Close
Set rs = Nothing

You'd be better off doing a Update Query:

CurrentProject.Connection.Execute "Update queryav set
[Booking ID]=" & varNewID


Chris Nebinger

-----Original Message-----
I'm trying to do something like the following but keep
getting error messages like type mismatch. What should
this look like?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "DoCmd.OpenQuery 'QueryAv'"
rs![Booking ID] = varNewID
rs.Update
rs.Close
Set rs = Nothing

QueryAV is:

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Day Number],
AVAILABILITY.[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));

.
Click to expand...
.
Click to expand...
 
Not quite sure what the format of the following statement
would be to execute QueryAV. What other statements are
needed?


CurrentProject.Connection.Execute "Update QueryAv set
[Booking ID] = " & varNewID

-----Original Message-----
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from
QueryAv",CurrentProject.Connection,adOpenDynamic,adLockOp ti
mistic
rs![Booking ID] = varNewID
rs.Update
rs.Close
Set rs = Nothing

You'd be better off doing a Update Query:

CurrentProject.Connection.Execute "Update queryav set
[Booking ID]=" & varNewID


Chris Nebinger

-----Original Message-----
I'm trying to do something like the following but keep
getting error messages like type mismatch. What should
this look like?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "DoCmd.OpenQuery 'QueryAv'"
rs![Booking ID] = varNewID
rs.Update
rs.Close
Set rs = Nothing

QueryAV is:

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Day Number],
AVAILABILITY.[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));

.
Click to expand...
.
Click to expand...
 
Tried CurrentProject.Connection.Execute "Update queryav
set [Booking ID]=" & varNewID

and got the no value given for one or more required
parameters. Any ideas? Thanks.
-----Original Message-----
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from
QueryAv",CurrentProject.Connection,adOpenDynamic,adLockOp ti
mistic
rs![Booking ID] = varNewID
rs.Update
rs.Close
Set rs = Nothing

You'd be better off doing a Update Query:

CurrentProject.Connection.Execute "Update queryav set
[Booking ID]=" & varNewID


Chris Nebinger

-----Original Message-----
I'm trying to do something like the following but keep
getting error messages like type mismatch. What should
this look like?

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "DoCmd.OpenQuery 'QueryAv'"
rs![Booking ID] = varNewID
rs.Update
rs.Close
Set rs = Nothing

QueryAV is:

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.[Day Number],
AVAILABILITY.[Booking ID]
FROM AVAILABILITY
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));

.
Click to expand...
.
Click to expand...
 
Back
Top