Writing a variable to a table

[Kanga]

Hi everybody,

I'm using Access 2010 and am trying to pass a variable in a form field and store it in a table i.e. I have a customer database with various fields: Customer_ID, Customer_Name, Customer_Address etc.
After selecting a particular record i.e. Customer_ID = 20 l need to store the record number '20' in a temporary table.
Could anybody give me any idea and any sample code for what is the simplest way to proceed.
I presume that l could then use Dlookup to read the info back at a later stage.
Look forwarding to hearing from you.
Thanks.

 

[Pat Hartman]

I would use a subform bound to the the temp table. I would use a combo to select the customer. The user could add as many rows as he wanted. This method won't require any code.

 

[Solo712]

Hi everybody,

I'm using Access 2010 and am trying to pass a variable in a form field and store it in a table i.e. I have a customer database with various fields: Customer_ID, Customer_Name, Customer_Address etc.
After selecting a particular record i.e. Customer_ID = 20 l need to store the record number '20' in a temporary table.
Could anybody give me any idea and any sample code for what is the simplest way to proceed.
I presume that l could then use Dlookup to read the info back at a later stage.
Look forwarding to hearing from you.
Thanks.

Are you selecting one Customer_ID or a number of them ?

What do you intend to do with the stored variable ? Are you passing it to another form or report as a filter or an argument ? Or do you need a bookmark for a specific customer record to return to in the same form ?

Best,
Jiri

 

[Kanga]

Thanks
Solo712 - I would be selecting the one CustomerID and storing that so at a later date (even after closing the program) it could be brought back in to generate a report.

 

[Solo712]

Thanks
Solo712 - I would be selecting the one CustomerID and storing that so at a later date (even after closing the program) it could be brought back in to generate a report.

If at some later point you want to store another ID in the table you would replace the existing variable, correct ? Or would you add a new record to the table ?

Best,
Jiri

 

[Kanga]

Hi Jiri
I would replace the existing variable. This table would be stored locally on each users computer to create an individuals record.
Regards
Kanga

 

[Solo712]

Hi Jiri
I would replace the existing variable. This table would be stored locally on each users computer to create an individuals record.
Regards
Kanga

Hi Kanga,
You might, as an option to creating copies of the same table, want to keep this variable in a sequential I/O file. If you want to have it as a table to be kept locally, you will have to split the database manually and include this table with the front-end objects rather than the other tables in the back-end. This is something of a chore.

If you want to use I/O file you need to place this into the sub which responds to a click or a press of a save button:

Dim ffile as String
ffile = Application.CurrentProject.path + "\MyCustId.txt"
Open ffile For Output As #1
Write #1, MyVariable ' replace with your variable name
Close #1

To retrieve the variable :

Dim ffile as String
ffile = Application.CurrentProject.path + "\MyCustId.txt" 
Open ffile For Input As #1
Input #1, MyVariable 'replace with your variable name
Close #1

You might want to retrieve the variable in the Form's OnLoad event and store it as a global variable if you have multiple uses for it while running the program.

Best,
Jiri

 

[Kanga]

Hi Jiri - perfect...

Thats just what l wanted, works perfectly.

Thanks very much.

Regards
Kanga