Update a table selected by the user? 1

[Xenocide]

is there a way to Update a table selected by the user

Exemple :

the user select from two listbox 2 ID. (lst1, lst2)

Then he enter a number in a textbox (txt1).

Then I do a calcul so to substract the number in txt1 from
the money that was linked to lst1.
I do the same for lst2 but I add instead

Here come my problem. The ID he pick in the 2 listbox can come from 2 different table. Table807 and Table850.

That mean the id in lst1 can either be from table807 and table850. Because of that I can't do a fixed update that will tell.. go uptate that in the table850.

I think that exemple.
UPDATE [Forms]![TransfertBudget]![txt3] SET ...
would work if the user would enter the name of the table
in txt3.

But I would like the information of the table name to come from the previous select I did for the 2 listbox. Is there a way?

 

[PHV]

I guess the RowSource of either ListBox is an UNION query.
Simply add the table name in the query, like this:
SELECT "Table807" As Origine, Somefield FROM Table807
UNION ALL SELECT "Table850", Somefield FROM Table850

Then, using the Column property of the ListBox object you may build on the fly your UPDATE sql string.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Xenocide]

Thanks a lot for your help

How do I set the column as the table in the update

seem like it won't work with the usual
[Forms]![nameofforms]![nameoflist]

 

[PHV]

When in VBE find the ListBox object in the leftmost pane of the object browser (F2), then click the Column property and press F1.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Xenocide]

it doesn't really help to know how to write the string for the table in the update query

still say the usual Forms!Formulaire!Listbox.Column(0)

but the query does an error and point to the first !

 

[PHV]

strSQL="UPDATE [" & Me!ListBox.Column(0) & "] SET ..."

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Xenocide]

will prally work well this way since yur the one who gave it.. but do you know if there,s way to do it in a query.. that's what I've been trying

This kind of stuff the Me! or the Forms! isn't taken by the query when it come to the table name. I was wondering if there was another to do it... I would rather do it in a separate query but if this is the only way well I'll take it

 

[PHV]

In query, parameters are used only for values, not for keyword not tablename nor fieldname ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Xenocide]

ok thanks that's what I wanted to know

I was trying all along to make it work in a query