Insert Statement Help

[PRodgers4284]

I have an insert statement inside i command button ("Add") on a form and the code at present adds the values from two combo boxes populated from two tables (Hospital and Ward). The insert statement inserts the values into a table called "Admission", at the minute the combo box values for the hospital and ward are being added to the Admissions table but I have added a textbox to the form called "Question" and im having difficulty inserting this textbox value into the Admissions table. Can anyone help?

The code I have at the min for the add command button is:

Private Sub btnSaveToAdmissions_Click()
Dim rs As DAO.Recordset
Dim qDef As DAO.QueryDef
Set qDef = CurrentDb.CreateQueryDef("")
qDef.SQL = "INSERT INTO Admission (WardName, HospitalName) VALUES (@WardName, @HospitalName)"
qDef.Parameters("@WardName").Value = Ward.Column(1)
qDef.Parameters("@HospitalName").Value = Hospital.Column(1)
qDef.Execute
MsgBox "Saved."
End Sub

 

[PRodgers4284]

I have tried the following code but i know the syntax is incorrect:


Private Sub btnSaveToAdmissions_Click()
Dim rs As DAO.Recordset
Dim qDef As DAO.QueryDef
Set qDef = CurrentDb.CreateQueryDef("")
qDef.SQL = "INSERT INTO Admission (WardName, HospitalName, Question) VALUES (@WardName, @HospitalName, @Question)"
qDef.Parameters("@WardName").Value = Ward.Column(1)
qDef.Parameters("@HospitalName").Value = Hospital.Column(1)
qDef.Execute
MsgBox "Saved."
End Sub

"Hospital" and "Ward" are the combo box options and "Question" is the textbox field in the form.

 

[DCrake]

Is appears to me you are trying to apply SQL syntax to Access logic.

The normal code would be as follows

Dim sSQL As String

sSQL = "INSERT INTO Admission (WardName, HospitalName, Question) SELECT '" &  Me.Ward.Coloumn(1) & "' As A, '" & Me.Hospital.Column(1) & "' As B, '" & Me.TextBox & "' AS C;"

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

There does not seem to be any need for the over enthusiastic coding.

David

 

[PRodgers4284]

Is appears to me you are trying to apply SQL syntax to Access logic.

The normal code would be as follows

Dim sSQL As String
 
sSQL = "INSERT INTO Admission (WardName, HospitalName, Question) SELECT '" &  Me.Ward.Coloumn(1) & "' As A, '" & Me.Hospital.Column(1) & "' As B, '" & Me.TextBox & "' AS C;"
 
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

There does not seem to be any need for the over enthusiastic coding.

David

Hey David thanks for that, ur code sames alot easier to use , i have a slight problem, when i select the add command to add the record to the admission table the record adds fine but when i close the form it adds another row in the table with the HopitalName and WardName empty and the Question field with the value that has been added in the form, the form is basically adding another table row when i close the form from the contents of the Question textbox, is there a way i can stop it form doing this and only add the record when the add command is selected?

 

[DCrake]

Have a lok at the form settings and see if the Allow Addtions is set correctly. Also make sure you are not running any code that is trying to add a record.