adding a new record in a form using a VBA procedure

G

Guest

I am new to Access, so I am afraid my question might be very simple, but I
have been unable to find an answer in the Knowledge base or documentation,
and lots of trial and error have failed to produce results.

I have a form that is used to create new records in a database. The main
table contains potential sales leads. One of the fields in this table
specifies which city the lead is in. The list of allowable cities is stored
in a separate table, and the leads table simply contains an index linked to
the primary key of the cities table.

when the user is entering a new lead he selects a city from a combo box (As
a result of the link between the tables, the combo box is populated
automatically with the names of all allowable cities, taken from the cities
table). The combo box has two columns: one with the index number of the city
and one with the name of the city.

I want the combo box to display the city names (not the indices) so I put
the names on the combo box's first column and the indices in the second
column. However, I want the leads table to store the index, not the name, so
the field is bound to the second column. As a result, access automatically
sets the "limit to list" property to true.

In certain cases I want to allow users to enter new cities that are not in
the list. I am doing this by using the "On not in list" event for the combo
box control. I execute some code to validate that the user is indeed allowed
to add this city, and then I would like to add a new record to the cities
table. This is where I am having problems. I have tried using AddNew, using a
SQL statement (INSERT INTO...), and using DoCmd, butnothing seems to work.

The form's data source is a query of the sales leads table. The query does
NOT include the cities table. I am not sure if this is part of the problem.

Caould someone please explain how I would go about adding a new record to
the cities table, from within a form whose data source is a query for another
table? I am using VBA to program the necessary code, and I am fairly new to
this, so please keep the explanations simple!

thanks in advance,

William
 
G

Guest

wgoldfarb,
here is a simple function to call from your "On not in list", all you have
to do is pass this function the new city name and it will add it to the
table. You will need to make sure the table name and field name are correct
as you did not provide that information I have used the generic titles of
tblCities and CityName also note that your database will need a reference to
Microsoft DAO to run this function. If you need help with this or anything
else please do not hesitate to request it.
Hope this helps! Take Care & God Bless ~ SPARKER ~

Call AddNewCity("InsertCityNameHere")

Function AddNewCity(pstrNewCityName As String)

Dim daoDbs As DAO.Database
Dim pstrSql As String

Set daoDbs = CodeDb

pstrSql = _
"INSERT INTO tblCities ( CityName ) " & _
"SELECT '" & pstrNewCityName & "';"

daoDbs.Execute pstrSql, dbSeeChanges

pstrSql = ""
daoDbs.Close

End Function
___________________________________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top