Insert values into a table from a combo box

Question

Hi

Form name: frmSchd

Combo Box name:  Combo42

The form also has controls: Account_Number with data source also Account_Number, DateAdm with DateAdm as data source

On this form if the user selects (via the combobox) HepBvaccine

Then I want that value of the combobox along with the value in the control Account_Number and DateAdm to be inserted into an unrelated table as a new row.

The unrelated table name: tblVaccine

It has fields named:  Account_Number, DateAdm and VaccineName (Ofcourse the combo box value need to be inserted into the VaccineName).

How can I accomplish this using the strSQL and INSERT functions. My biggest handicap is the poor understanding of proper use of various quotations and the use of &

Any help will be greatly appreciated

Thanks

HNIM2010

Answer 1

You can do it with code:

Dim strSQL As String

strSQL = "INSERT INTO tblVaccine(Account_Number, DateAdm, VaccineName) " & _

    "VALUES(" & Me.Account_Number & ",#" & Format(Me.DateAdm ,"yyyy-,mm-dd") & "#,""" & _

    Me.HepBvaccine & """)"

CurrentDb.Execute strSQL, dbFailOnEror

This assumes that Account_Number is a number data type, DateAdm a date/time data type, and VaccineName a text data type.

You say the table is unrelated, but reading between the lines, it looks to me as though tblVaccine is referencing whatever table the form is bound to in a many-to-one relationship type on Account_Number and DateAdm, which I take to be a composite candidate key of the referenced table, in which case you could do this without any code by means of a subform bound to tblVaccine and linked to the parent form on Account_Number;DateAdm.  All you'd then need would be a combo box in the subform bound to the VaccineName column from which to select the vaccine.

Answer 2

Use a command button to run an append query.

INSERT INTO tblVaccine ( Account_Number, DateAdm, VaccineName  )

SELECT [Forms]![frmSchd]![Account_Number], [Forms]![frmSchd]![DateAdm], [Forms]![frmSchd]![Combo42];

Answer 3

Can I use this as part of If...Then routine in the AfterUpdate Event of the combobox?

Actually that would be a good idea as you only want to execute the INSERT statement, whether you build it in code or as a saved query, if none of the controls are Null:

If Not IsNull(Me.Account_Number) _

    And Not IsNull(Me.DateAdmMe) _

    And Not IsNull(Me.HepBvaccine) Then

However, there is a possible fly in the ointment, which is a that a user might have selected the wrong vaccine inadvertently and inserted a row into the table.  They would then probably delete the entry in the combo box, making it Null, or select the correct vaccine, but neither of these would remove the incorrect row from the table.

You could provide a means of undoing an insertion by grabbing the vaccine name to a variable, and then executing a DELETE statement to delete the row with the Account_Number, DateAdm and VaccineName, or more simply you could require user validation of the insertion of the row in the If.....End If construct.  I still feel the best method would be a subform, however, as this is not only code-free, but makes it extremely simple for a user to delete an incorrect row.

You might think this sort of error will never happen, but Murphy's Law states otherwise.

Bearing in mind the apparent subject of your database you might find it worth taking a look at Treatments.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file is a simplified illustration of how to insert schedules of events at predetermined intervals, it just happens to use medical treatments as an example as it stemmed from something I once did for the medevac people.  You might possibly find something in it of use to you in your database.

Answer 4

Why would you need If..Then.. routine?  What I presented was an append query that could be run by AfterUpdate Event of the combobox

Answer 5

Hi Karl

Can I use this as part of If...Then routine in the AfterUpdate Event of the combobox?

Thanks

HNIM2010