SQL INSERT : What's wrong with this statement??

[Will04]

Hi everyone,

I'm trying to add a record to a table using the SQL INSERT statement, but somehow it's not working..

I have declared in a standard module some public variables, which are populated during data entry. After the data entry, I want to save a record using the public variables, but when the statement is executed I'm asked to supply values for the public variables.

Before the INSERT statement, I use msgbox to display the values and they are there, yet I'm being asked to enter values.

/code ****************

Private Sub da_Click()
MsgBox "<<<< setapptfrm >>>"
MsgBox "patient no >> " & strpatientno
MsgBox "Surname >> " & strpsname
MsgBox "first name >> " & strpfname
MsgBox "date of birth >> " & dtdob
MsgBox "address >> " & straddress

DoCmd.RunSQL "INSERT INTO tblappointments ([apptdate], [patientno], [pfname], [psname], [address],[dob]) VALUES ([cboapptdate],[strpatientno],[strpfname],[strpsname],[straddress],[dtdob]);"
DoCmd.Requery

stDocName = "frmpatientappointments"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

/code ********************


Any ideas?

Thanks


Will

 

[Moniker]

Change this:

VALUES ([cboapptdate],[strpatientno],[strpfname],[strpsname],[straddress],[dtdob]);

To this:

VALUES ("#" & [cboapptdate] & "#, '" & [strpatientno] & "', '" & [strpfname] & "', '" & [strpsname] & "', '" & [straddress] & "', #" & [dtdob] & "#")

You need to tell it what each type of data is because the VALUES list doesn't determine that for you. Therefore, strings get a single quote, dates get a pound (#) sign, and numbers do not get anything. I think I did yours right (first and last values are dates, the rest are strings).

 

[Will04]

Hi Moniker,
Thanks so much for responding..

I changed the VALUE clause as suggested, but when I debug, I'm getting a syntax error...

/********

DoCmd.RunSQL "INSERT INTO tblappointments ([apptdate], [patientno], [pfname], [psname], [address],[dob]) VALUES ("#" & [cboapptdate] & "#, '" & [strpatientno] & "', '" & [strpfname] & "', '" & [strpsname] & "', '" & [straddress] & "', #" & [dtdob] & "#")


/************

Did I leave out something??

Thanks again..


Will

 

[GolfProRM]

DoCmd.RunSQL "INSERT INTO tblappointments ([apptdate], [patientno], [pfname], [psname], [address],[dob]) 
VALUES (#" & [cboapptdate] & "#, '" & [strpatientno] & "', '" & [strpfname] & "', '" & [strpsname] & "', '" & [straddress] & "', #" & [dtdob] & "#)"

The code wasn't quite right -- there shouldn't be a quote before the first pound sign, and the end quote needs to be after the closing parenthesis.

 

[Will04]

Hi Ryan,

Thank You, it worked great.

One problem though, instead of storing the date of birth, I'm seeing this value...
12:00:00 AM.

Any idea what's happening?

I use this statement to assign the DOB entered to the public variable

/*********

dtdob = Dob
MsgBox "date of birth = " & Dob
MsgBox "public variable date of birth = " & dtdob

/************

Immediately after this statement, I display both variables and the value is the correct date of birth. However, when I click on a command button on the data entry form - appts, I again display the values of the public variables and this is where the value '12:00:00 AM' appears instead of the date of birth entered.

Private Sub appts_Click()
On Error GoTo Err_appts_Click


MsgBox "patient no >> " & strpatientno
MsgBox "Surname >> " & strpsname
MsgBox "first name >> " & strpfname
[COLOR="Red"]MsgBox "date of birth >>  & [dtdob]"[/COLOR]
MsgBox "address >> " & straddress


    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "setapptfrm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_appts_Click:
    Exit Sub

Err_appts_Click:
    MsgBox Err.Description
    Resume Exit_appts_Click
    
End Sub

Thanks again


Appreciated.

Will

 

[GolfProRM]

Access stores all date values as date/time, so when you enter just a date, access stores the date, and the time as midnight.

What you can do is set the format of any fields that use this date. You can set it to view the date as a short/long date format so you only see the date value.

 

[Brianwarnock]

Is this syntax correct?

MsgBox "date of birth >> & [dtdob]"
should it be
MsgBox "date of birth >>" & [dtdob]

Brian

 

[Will04]

Thanks guys.

Ryan - thanks for the tip
Brian - yes it's an error, it has been changed

After changing the date format to short in the form and the table, I'm now getting the date to be 1899/12/30....

Dunno what's going on here!!!


Help.. anyone???


Will

 

[gemma-the-husky]

1899/12/30 is actually date 0 in access

somehow you are losing the correct date, and inserting 0 into your table

 

[Will04]

Thanks Gemma,

Hmmmm..

Up to this point it's ok i.e. the dob entered is stored in the public variable dtdob

dtdob = Dob
MsgBox "date of birth = " & Dob
MsgBox "public variable date of birth = " & dtdob

Then I click on a button in the data entry form. In the on click property I again display the date, but at this point the correct value is lost.

This is weird...

Will

 

[GolfProRM]

is there a reason you need to store it in a public variable? Could your SQL statement just use Me.Dob as the value?

 

[Will04]

Hi Everyone,

PROBLEM SOLVED!!!!!!!!!

Apparently I was using the variable name 'dtdob' in a sub to calculate age and the same variable was declared as the public variable... Whew!!!!



Thanks a million, Appreciate all the help.


Will

 

[Will04]

Ryan,

What I'm working on is a scheduling/appointment module for a screening application.
The patient comes in for a test, is examined and the patient demographics and examination data is entered. At this point the appointment module is selected to schedule the patient's return to collect the test results. I use the public variables to store the required values from the data entry form which will then be written to the appointments table. Once a valid date is selected, the appointments table is queried and all the appointments for that day is displayed.

Maybe there is an easier or more efficient way to accomplish this, but this works for me..

Makes sense???


Will

 

[GolfProRM]

Gotcha... Didn't realize you were holding the data from a previous form.

As long as it works, that's all that matters!

Glad it's working now!

 

[Brianwarnock]

Hi Everyone,

PROBLEM SOLVED!!!!!!!!!

Apparently I was using the variable name 'dtdob' in a sub to calculate age and the same variable was declared as the public variable... Whew!!!!



Thanks a million, Appreciate all the help.


Will

This is where naming conventions score, I used to prefix all my public varaibles with pub, so pubdtDate. I bet there is a standard out there.

Brian

 

[Will04]

Yep Brian...

I could have saved myself a lotta frustrating hours trying to figure out why it wasn't working...

Thanks again everyone.


Will

 

[gemma-the-husky]

you can step through code to see what is happening to your data

put a breakpoint in the code, then use F8 to step through.

Useful to find these hard to pin down errors, and to trace your programme execution

 

[Will04]

Thanks for the tip Gemma

Appreciated


Will