Update VBA/SQL Statement (1 Viewer)

[esymmonds]

Hi,

Still trying to make my booking system! I'm trying to make a loop that will check in a guest for each booking night of stay. (each booked night has 1 record and all it needs to do is give checkin a true value).

Now the query part isn't working. It works as an access query but not when i convert it to vba. Am working on the first query at the momment, not the second.

Private Sub checkin_button_Click()

Dim dteBookDate As Date
Dim nights As Integer
Dim count As Integer

dteBookDate = CDate(Forms("form").date)
nights = Forms("form").nights
count = 0

Do While count < nights

   DoCmd.RunSQL "UPDATE table1 SET table1.checkin = True WHERE (((book_room)=" & Forms("form").[roomno] _
                   & ") AND ((date)= #" & dteBookDate & "#));"

   DoCmd.RunSQL "INSERT INTO breakfastpaper VALUES (#" & dteBookDate & "#, " & Forms("checkin2").[roomno] & "," _
                  & Forms("checkin2").[breakfast] & ", " & Forms("checkin2").[paper_code] & ");"

   dteBookDate = DateAdd("d", 1, dteBookDate)
   count = count + 1
Loop
DoCmd.RunMacro "checkin2"
End Sub

I'm thinking it's just something small (hopefully). I just can't find it. I've attatched a little test database that I have been using to try and figure it out.

Any help is greatly appreciated.

Emma



My other posts to do with my booking system can be found:
Booking System Dates
Insert Statement

 

[WayneRyan]

Emma,

What's not working the Update or the Insert?

Are you getting an error?

I still vote for explicitly declaring the columns in the Insert
statement.

Wayne

 

[esymmonds]

It's the update, I'm getting good at inserts now thanks to all your help!

I tried explicitly declaring the feilds but i got less errors when i didn't so i took them out.

Emma

 

[WayneRyan]

Emma,

You have: Forms("form").[roomno]

You need: Forms("form").[room_no]

Or just: Me.room_no

Wayne

 

[esymmonds]

Opps!

OK, I've corrected that but I'm still getting:

Run Time error 3464

Data type mismatch in criteria expression


Emma

 

[WayneRyan]

Emma,

Instead of: SET table1.checkin = True

Use: SET table1.checkin = -1

Wayne

 

[esymmonds]

I will do, but that hasn't changed anything.

Emma

 

[WayneRyan]

Emma,

It'll never match, You are setting the default value for date
to =Now(). This DateTime field (emphasis on TIME) will never
match your short date in Table1.

Change it to =Date()

Wayne

 

[WayneRyan]

Emma,

You also can't call your date field "Date". This is reserved for use
by Access.

Wayne

 

[esymmonds]

Hi Wayne,

Will try that when i get home, although i didn't use those variable names in the proper booking system and it doesnt work in there.

Also, I set date to now() so that I didn't have to keep typing it in! Just laziness really so I'll take that out!

Is the syntax of my statement correct?

Emma

 

[esymmonds]

Have tried it again but I'm still getting the same error

Emma

 

[WayneRyan]

Hi Emma,

My Internet connection died on my last response.

You can't use Date as a column name in your table definition.
It is reserved by Access.

When you set your default value for your "Date" field to Now()
you are doing two things wrong.

Date is a reserved word for a Table's column name.

When you set it's default value to =Now(). The Now()
function returns a date/TIME stamp. It will never match
the short dates in your table.

Short date = #02/17/2004"
Long date = #02/17/2004 18:20:00 PM"

Change the date field on your table to "SomeDate"
Change the default value to "=Date()"

Wayne

 

[esymmonds]

Hi Wayne,

Thanks for all your help, I've got it working now.

The problem was that room_no wanted text and the room number was an integer so I just changed the feild type and it worked!

Thanks again, your a genius!

Emma