Inserting record from Dropdown List

[dbalorenzini]

I have a form that has a status drop down list on it. What I need to happen
is when the status in this dropdown list is changed, I need to add a record
into a table called tblStatus. What I need inserted is the current clientid,
date the change happen (Date()),and the Status change from the dropdown list
("Active","Inactive", "Dropped", "Closed"). Any ideas?

 

[John Spencer]

Barebones VBA that you can use in either the after update event of the
combobox or in the Before update event of the form.

This may fail if you are creating a new record in the main table and
have referential integrity set up between tblStatus and the main table.
If that is the case you are going to need to commit the record in the
main table before you attempt to add a record. So you will need to set
a variable to track the status change (if any) and use the form's after
update event to create the new record or not depending on the value of
the variable.


Dim strSQL as String

If Me.ComboStatus & "" <> Me.Combostatus.OldValue & "" THEN
strSQL = "Insert Into tblStatus(ClientID,ChangeDate,Status)" & _
" Values("& Me.ClientID & "," & _
Format(Date(),"\#yyyy-mm-dd\#") & _
""" & Me.comboStatus & """)"

CurrentDb().Execute strSQL
END IF

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[dbalorenzini]

Yes, that was exactly what I needed.