saving by code... (1 Viewer)

[hoschi]

I got three combo boxes with this code...
one table: tblTest with these attributes: land = country, stadt = city and einwohner = habitant

the boxes are filtered, so i can only choose the cities which are in the country i decided in the box above...

box 1
SELECT DISTINCT tblTest.land FROM tblTest ORDER BY tblTest.land;

box 2
SELECT DISTINCT [tblTest].[stadt], [tblTest].[land] FROM tblTest WHERE ((([tblTest].[land])=[Forms]![frmTest1]![land])) ORDER
BY [tblTest].[stadt];

box 3
SELECT DISTINCT [tblTest].[einwohner], [tblTest].[stadt] FROM tblTest WHERE
((([tblTest].[stadt])=[Forms]![frmTest1]![stadt])) ORDER BY [tblTest].[einwohner];

i want to add to the form a button which saves the chosen information in a other tabel perhaps tblTest2...

how can i do this...

thanx
hoschi

 

[FoFa]

Are you trying to save the Criteria that was selected, or some data based on the criteria?
I would just create a query that either pulls the criteria from the form and saves it, or pulls data from where ever pulling the criteria from the form.

 

[hoschi]

yes, I'm trying to save the criteria that was selected!

By pressing the button a code should run, which saves the selected criteria in another table!

I tried the following:
[Forms]![frmTest1]![land] = [tblTest2].[land]


but it does not work!!!

any ideas???

hoschi

 

[ghudson]

I prefer to use an update query (SQL) to update the "criteria" textbox fields to the criteria table. Just run it from a command button or the OnClose event of the form.

HTH

 

[hoschi]

my problem is that I don't know how to store the selected criteria into another table!!!

hoschi

 

[Nouba]

you can find a method on Allen Bowne's Site Return to the same record next time form is opened

 

[hoschi]

sorry Nouba, that is not what i'm looking for!!!

my problem is that I don't know how to store the selected criteria into a different table!!!

hoschi

 

[Matthew Snook]

Something akin to the following code would use the SQL idea mentioned above to insert data from your combo box into another table...

Dim sqlAppend As String
sqlAppend = "INSERT INTO tblTest2 ( Land) SELECT " & Me.ComboName & " AS Land;"
DoCmd.RunSQL sqlAppend


Matt

 

[hoschi]

here's an example of my problem

In my example there are two tables, tblSource (with the informations) and tblDestination (where I want to put the Infos with some more Infos).

In the form frmTest I used these SQL Statements to select from the Information Table tblSource.

There is a button btnSave: I want by clicking this button the selected criteria in the 4 comboboxes copied into the Table tblDestination (with some new Infos just like "description")!

How can I do this?

hoschi

 

[Matthew Snook]

Try this (there is no error trapping included; remove the comment quote-mark to use the SetWarnings code):

Private Sub btnSave_Click()
'DoCmd.SetWarnings False
Dim sql_destination As String
sql_destination = "INSERT INTO tblDestination ( country, city, last_name, first_name, description ) " & _
"SELECT [Forms]![frmTest]![country] AS new_country, [Forms]![frmTest]![city] AS new_city, " & _
"[Forms]![frmTest]![last_name] AS new_last, [Forms]![frmTest]![first_name] AS new_first, " & _
"'enter some text here' AS new_text;"
DoCmd.RunSQL sql_destination
'DoCmd.SetWarnings True
End Sub


Matt

 

[hoschi]

thanks a lot!

it works great...

hoschi