Insert statement in VBA (1 Viewer)

E

esymmonds

Registered User.
Local time
Today, 01:17
Joined
Jan 26, 2004
Messages
36
Cant run my Insert statement in VBA, Please help me

Hello,

Can someone help me? I've been working on this for a while now and my original question is here: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=60929

Basicaly I'm making a booking system and i needed some code that will insert a booking record for each night of stay. So date 1 is arrival date and date 2 is departure date. So the statement should insert 3 values into the table, only there's a syntax error with the sql statement, i think it's because I'm not refering to the boxes on my form properly but can't find how I should do it.

I've made the module with the following code:

Private Sub add_booking_Click()
Dim BookDate As Date
Dim LeaveDate As Date
BookDate = Forms!Form!date1
LeaveDate = Forms!Form!date2
Do While BookDate < LeaveDate
DoCmd.RunSQL "INSERT INTO booking VALUES (Forms!form!room_no.text, bookdate, Forms!form!cust_no.text"
BookDate = BookDate + 1
Loop
End Sub

All help is very much appriciated.

Emma
 
dcx693

dcx693

Registered User.
Local time
Yesterday, 20:17
Joined
Apr 30, 2003
Messages
3,265
Without looking at the rest of the code, I can see that your DoCmd.RunSQL string does not have a closing parenthesis.
 
E

esymmonds

Registered User.
Local time
Today, 01:17
Joined
Jan 26, 2004
Messages
36
Oh Yes, well spotted!

It still dosen't work tho, It's started asking me for the parameter value for bookdate, so i tried having it look at:

BookDate = Forms!Form!date1.text

But then it says you can't use the value if its not in focus. I can't have all the information and the button in focus.

Any Ideas?

Emma
 
Mile-O

Mile-O

Back once again...
Local time
Today, 01:17
Joined
Dec 10, 2002
Messages
11,316
How about this? Is your form really called form?

Code: 
Private Sub add_booking_Click()

    On Error GoTo Err_add_booking_Click

    Dim dteBookDate As Date
    Dim dteLeaveDate As Date
    Dim strSQL As String
    
    dteBookDate = CDate(Forms("Form").date1)
    dteLeaveDate = CDate(Forms("Form").date2)
    
    strSQL = "INSERT INTO booking VALUES (" & Forms("form").[room_no] _
        & ", " & dteBookDate & ", " & Forms("form").[cust_no] & ");"
    
    Do While dteBookDate < dteLeaveDate
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        dteBookDate = DateAdd("d", 1, dteBookDate)
    Loop
    
Exit_add_booking_Click:
    strSQL = vbNullString
    Exit Sub
Err_add_booking_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_add_booking_Click
    
End Sub
 
Mile-O

Mile-O

Back once again...
Local time
Today, 01:17
Joined
Dec 10, 2002
Messages
11,316
esymmonds said:
But then it says you can't use the value if its not in focus. I can't have all the information and the button in focus.
Click to expand...

You need to be on the control to use the .Text property. You can drop the .Text property anyway.
 
E

esymmonds

Registered User.
Local time
Today, 01:17
Joined
Jan 26, 2004
Messages
36
Mile-O-Phile said:
How about this? Is your form really called form?

Code: 
Private Sub add_booking_Click()

    On Error GoTo Err_add_booking_Click

    Dim dteBookDate As Date
    Dim dteLeaveDate As Date
    Dim strSQL As String
    
    dteBookDate = CDate(Forms("Form").date1)
    dteLeaveDate = CDate(Forms("Form").date2)
    
    strSQL = "INSERT INTO booking VALUES (" & Forms("form").[room_no] _
        & ", " & dteBookDate & ", " & Forms("form").[cust_no] & ");"
    
    Do While dteBookDate < dteLeaveDate
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        dteBookDate = DateAdd("d", 1, dteBookDate)
    Loop
    
Exit_add_booking_Click:
    strSQL = vbNullString
    Exit Sub
Err_add_booking_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_add_booking_Click
    
End Sub
Click to expand...

It's not really called form in the booking system, i'm just using 1 table and form in another database until I get the module working then I'll ammend it and put it in its rightful place.

I used this code but I'm getting a syntax error in query expression '2'

Emma
 
Last edited:
Mile-O

Mile-O

Back once again...
Local time
Today, 01:17
Joined
Dec 10, 2002
Messages
11,316
I've never actually seen the INSERT INTO Table VALUES structure in SQL before so it's alien to me.

Maybe DAO or ADO, depending on your version of Access may be what you are after to put some values into a table. Field names and object names, of course, are assumed.

i.e.

Access 97 - DAO
Code: 
Private Sub add_booking_Click()

    On Error GoTo Err_add_booking_Click

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim dteBookDate As Date
    Dim dteLeaveDate As Date
    
    dteBookDate = CDate(Forms("Form").date1)
    dteLeaveDate = CDate(Forms("Form").date2)

    Set db = CurrentDb
    Set rs = db.OpenRecordset("booking")

    With rs
        Do While dteBookDate < dteLeaveDate
            .AddNew
            .Fields("room_no") = Forms("form").[room_no]
            .Fields("bookingdate") = dteBookDate
            .Fields("cust_no") = Forms("form").[cust_no]
            .Update
            dteBookDate = DateAdd("d", 1, dteBookDate)
        Loop
        .Close
    End With

    db.Close
    
Exit_add_booking_Click:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

Err_add_booking_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_add_booking_Click
    
End Sub

Access 2000 (or above) - ADO
Code: 
Private Sub add_booking_Click()

    On Error GoTo Err_add_booking_Click

    Dim cn As ADODB.Database
    Dim rs As ADODB.Recordset
    Dim dteBookDate As Date
    Dim dteLeaveDate As Date
    
    dteBookDate = CDate(Forms("Form").date1)
    dteLeaveDate = CDate(Forms("Form").date2)

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    rs.Open "booking", cn, adOpenStatic, adLockPessimistic

    With rs
        Do While dteBookDate < dteLeaveDate
            .AddNew
            .Fields("room_no") = Forms("form").[room_no]
            .Fields("bookingdate") = dteBookDate
            .Fields("cust_no") = Forms("form").[cust_no]
            .Update
            dteBookDate = DateAdd("d", 1, dteBookDate)
        Loop
    End With
    
Exit_add_booking_Click:
    Set rs = Nothing
    Set cn = Nothing
    Exit Sub

Err_add_booking_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_add_booking_Click
    
End Sub
 
E

esymmonds

Registered User.
Local time
Today, 01:17
Joined
Jan 26, 2004
Messages
36
Private Sub add_booking_Click()

On Error GoTo Err_add_booking_Click

Dim dteBookDate As Date
Dim dteLeaveDate As Date
Dim strSQL As String

dteBookDate = CDate(Forms("Form").date1)
dteLeaveDate = CDate(Forms("Form").date2)

strSQL = "INSERT INTO booking VALUES (" & Forms("form").[room_no] _
& ", " & dteBookDate & ", " & Forms("form").[cust_no] & ");"

Do While dteBookDate < dteLeaveDate
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
dteBookDate = DateAdd("d", 1, dteBookDate)
Loop

Exit_add_booking_Click:
strSQL = vbNullString
Exit Sub
Err_add_booking_Click:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_add_booking_Click

End Sub
Click to expand...

I'ts kind of working using the above code except that the loop is not excecuting properly. It is only adding one record with the date 30/12/1899 which is the default date that all other dates are calculated from so there's something not quite right.

There is a test copy at esymmonds.tripod.com//db1.mdb

Any help is greatly appreciated.

Emma
 
W

WayneRyan

AWF VIP
Local time
Today, 01:17
Joined
Nov 19, 2002
Messages
7,122
Emma,

I can't read your download, but maybe this will help:

Code: 
Private Sub add_booking_Click()

On Error GoTo Err_add_booking_Click

Dim dteBookDate As Date
Dim dteLeaveDate As Date
Dim strSQL As String

dteBookDate = CDate(Forms("Form").date1)
dteLeaveDate = CDate(Forms("Form").date2)

Do While dteBookDate < dteLeaveDate
   strSQL = "INSERT INTO booking (RoomNumber, BookDate, CustomerNumber) " & _
            "VALUES (" & Me.[room_no] & ", #" & dteBookDate & "#, " & _
            Me.[cust_no] & ");"
   DoCmd.RunSQL strSQL
dteBookDate = DateAdd("d", 1, dteBookDate)
Loop

Exit_add_booking_Click:
   strSQL = vbNullString
   Exit Sub
Err_add_booking_Click:
   MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
   Resume Exit_add_booking_Click

End Sub

You have to rebuild the SQL string each time with the loop.

Wayne
 
E

esymmonds

Registered User.
Local time
Today, 01:17
Joined
Jan 26, 2004
Messages
36
well I've used bits of all the code you helpful lot have given me, and I've steped through the program and all the right values are in the right places when they should be, but it's still putting the date in the database as 30/12/1899, even though the date is changing properly throughout the loop.

Any ideas?
the loop code is:

Do While dteBookDate < dteLeaveDate
strSQL = "INSERT INTO table1 VALUES (" & Forms("form").[room_no] _
& ", " & dteBookDate & ", " & Forms("form").[cust_no] & ");"
DoCmd.RunSQL strSQL

dteBookDate = DateAdd("d", 1, dteBookDate)
Loop

Emma
 
W

WayneRyan

AWF VIP
Local time
Today, 01:17
Joined
Nov 19, 2002
Messages
7,122
Emma,

Format the date. I still vote for explicitly stating what
columns to put the values in.

Code: 
Do While dteBookDate < dteLeaveDate
   strSQL = "INSERT INTO booking (RoomNumber, BookDate, CustomerNumber) " & _
            "VALUES (" & Me.[room_no] & ", #" & Format(dteBookDate, "dd-mmm-yyyy") & "#, " & _
            Me.[cust_no] & ");"
   DoCmd.RunSQL strSQL
   dteBookDate = DateAdd("d", 1, dteBookDate)
Loop

If that doesn't work (it should), post the db (compact/repair - zip - attach).

Wayne
 
E

esymmonds

Registered User.
Local time
Today, 01:17
Joined
Jan 26, 2004
Messages
36
still dosen't work, I'm going to fail.
 

Attachments

  • db1.zip
    23.9 KB · Views: 150
W

WayneRyan

AWF VIP
Local time
Today, 01:17
Joined
Nov 19, 2002
Messages
7,122
Emma,

I still vote for explicitly declaring the columns in the Insert
statement, but if you put the "#"s back in your code around
the date it will work.

I know ... I tried it.

Wayne
 
E

esymmonds

Registered User.
Local time
Today, 01:17
Joined
Jan 26, 2004
Messages
36
Thank you sooooo much to everyone for all your help.

I really really appreciate it. it works now yeah!

Thanks again
Emma
xxx
 
You must log in or register to reply here.

Similar threads

O
VBA Insert Into
Replies
2
Views
375
Pat Hartman
P
M
insert into code
Replies
2
Views
222
mhakim
M
A
Inserting numeric values into a Table, using SQL : What's the correct VBA code to use in the VALUES part of the code ?
Replies
10
Views
474
ebs17
E
C
Insert into syntax error
Replies
8
Views
431
ClaraBarton
C
H
avoiding repetitive records in CurrentDb.Execute InsertQuery
Replies
11
Views
411
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom