easier way to pupulate form

[ryetee]

I have a continuous form that the user can enter a number of items.
he does this by selecting each item from a drop down box. the drop down box can contain quite a few items for each customer. doing it this way it is slow and laborious.
I'd like to display on separate form all of the available items to him. he can then check the ones he wants and on closing the form the original is populated with the selected items.
I can't get my head around how to do this. Do I need the check box on the actual table he is selecting from., how do i stop any contention from another user.
Must be a long day as I can't think how to do this.

 

[theDBguy]

Hi. One option is to use a multi-select listbox. When ready, you can loop through the selected items and add each one to the table.

 

[pbaldy]

An example of what theDBguy is suggesting:

http://www.baldyweb.com/MultiselectAppend.htm

 

[ryetee]

Hi. One option is to use a multi-select listbox. When ready, you can loop through the selected items and add each one to the table.

Never used one but looks like pbaldy has pointed me in the right direction.
Thanks

 

[ryetee]

An example of what theDBguy is suggesting:

http://www.baldyweb.com/MultiselectAppend.htm

Excellent thank you

 

[theDBguy]

Never used one but looks like pbaldy has pointed me in the right direction.
Thanks

Good luck!

 

[pbaldy]

Excellent thank you

Happy to help! Post back if you get stuck.

 

[ryetee]

I've started to look at this but not sure if will meet what I want to do.
The user will select a number of items. Looks like this is fairly easy to do with the multi select list box. The other thing he'll want to to do is add a number in as well. Now I know he can go and do that after but his preference would be select item change number select n more change n numbers. Click finish and original form is filled in. Will i be able to do this with the multi select list box?

 

[theDBguy]

I've started to look at this but not sure if will meet what I want to do.
The user will select a number of items. Looks like this is fairly easy to do with the multi select list box. The other thing he'll want to to do is add a number in as well. Now I know he can go and do that after but his preference would be select item change number select n more change n numbers. Click finish and original form is filled in. Will i be able to do this with the multi select list box?

Hi. If you're saying the numbers will be different for each item selected, then you could probably use temporary table instead of a listbox. In this case though, there's no need to populate and empty the temp table everytime since the list of items should be the same every time. All you have to do is clear out the numbers.

 

[Uncle Gizmo]

I think your question is similar to something I've explained before, which you can see on my website here:- How to add a Checklist to MS Access ...

A common requirement is for a set of check-boxes. A common solution is to add an individual field for each checkbox, which you may be aware is not considered good practice. You are far better off having the check boxes in a single field in a table. In the series of YouTube videos on my website I demonstrate how to create a table to hold the fields that that get added to your checkbox form, and how to record the answers.

 

[ryetee]

Also....the items are split into different tariffs for different customers. Each tariff set has a number of items. The tariff sets are held on one table. I've just set up a quick listbox to play around with. I did this using form design and adding a list box onto the form and going through the dialogue asking me what fields I want etc. I now have something I can play around with but I can't see how to limit the displayed records to the tariff set id.

 

[ryetee]

Hi. If you're saying the numbers will be different for each item selected, then you could probably use temporary table instead of a listbox. In this case though, there's no need to populate and empty the temp table everytime since the list of items should be the same every time. All you have to do is clear out the numbers.

Not sure you're sure of what I want!
There are a set of tariff groups that contain anywhere between 10 and 90 entries. Each customer has a tariff group assigned to him. A tariff group can have more than 1 customer. All the tariff groups are held on one table. There is a default number of each item to buy. To keep it simple the tariff group will look like this
Item default number
Itm01 10
Itm02 5
Itm03 1
.
.
.
Itm99 15

First of all for client x I just want to display his tariff group. The one above.
Now for his order I might select Itm01, Itm03 and Itm99. When selection is taking play I want to be able to change the default number, or leave it alone.
That's the question I'm asking.

 

[theDBguy]

Not sure you're sure of what I want!
There are a set of tariff groups that contain anywhere between 10 and 90 entries. Each customer has a tariff group assigned to him. A tariff group can have more than 1 customer. All the tariff groups are held on one table. There is a default number of each item to buy. To keep it simple the tariff group will look like this
Item default number
Itm01 10
Itm02 5
Itm03 1
.
.
.
Itm99 15

First of all for client x I just want to display his tariff group. The one above.
Now for his order I might select Itm01, Itm03 and Itm99. When selection is taking play I want to be able to change the default number, or leave it alone.
That's the question I'm asking.

So, instead of "clearing" the temp table, then we simply "reset" it to the default values. I say "temp" table because I am referring to a local copy of the table to avoid multiple users from stepping on each others' toes when trying to make the selections at the same time.

 

[ryetee]

So, instead of "clearing" the temp table, then we simply "reset" it to the default values. I say "temp" table because I am referring to a local copy of the table to avoid multiple users from stepping on each others' toes when trying to make the selections at the same time.

So I create a temp table with the details of the tariff group display that and then copy the selected items to the order?

 

[theDBguy]

So I create a temp table with the details of the tariff group display that and then copy the selected items to the order?

Correct! I was suggesting to replace the listbox approach with a temp table approach. So, after you create a local copy of the tariff table, you can create a query to filter the records to what is applicable to the user. You would then display this using maybe a popup continuous form. You could add a checkbox to the table, so the user can simply check the ones they want to use. The user can then replace the default values of the numbers or not. On a click of a button, you can then transfer all the selected records into the actual child table you're currently using and then reset the temp table to remove all the check marks and put back the default numbers.


PS. The above is just one idea. You (or somebody else) could probably take it and improve on it and come up with a better one. I can already think of one where the temp table only has the item and a blank field for the number. The selection form will do all the work (including assigning the default values). This way, it's a simple delete all numbers from the temp table for the next time it is needed.

 

[ryetee]

Correct! I was suggesting to replace the listbox approach with a temp table approach. So, after you create a local copy of the tariff table, you can create a query to filter the records to what is applicable to the user. You would then display this using maybe a popup continuous form. You could add a checkbox to the table, so the user can simply check the ones they want to use. The user can then replace the default values of the numbers or not. On a click of a button, you can then transfer all the selected records into the actual child table you're currently using and then reset the temp table to remove all the check marks and put back the default numbers.


PS. The above is just one idea. You (or somebody else) could probably take it and improve on it and come up with a better one. I can already think of one where the temp table only has the item and a blank field for the number. The selection form will do all the work (including assigning the default values). This way, it's a simple delete all numbers from the temp table for the next time it is needed.

This is what I had in mind but using the original table which would screw up if more than one user was creating an invoice at the same time. In that scenario when temp tables are created are they specific to the user and won't interfere with another user doing the same at same time?

 

[theDBguy]

This is what I had in mind but using the original table which would screw up if more than one user was creating an invoice at the same time. In that scenario when temp tables are created are they specific to the user and won't interfere with another user doing the same at same time?

Exactly! If you put the temp table in the front end, then each user will be using their individual copy of the table to select from. Originally, I said the table doesn't have to be emptied and repopulated. However, if the list could change at any time, then it's safer to destroy or empty the temp table and populate it again with data from the linked table just to make sure the user sees all possible options.

 

[ryetee]

OK I'm getting stuck here.
I've set up a mock form where I create a temporary table with selected data (only use certain fields and also have a filter) from another table. I do this in the open event. I try and make this the record set using
Set db = CurrentDb
Set rs = db.OpenRecordset("TEMPTABLE")
Set Me.Recordset = rs
but get error 7965 as at this moment in time the TEMPTABLEe doesn't exist. Moved code to the current and then the load event but still the same. I can see that the table doesn't exists. If I by pass the above 3 lines table appears.
How do I get around this. I could set the table up in the form that opens this one I suppose.
That begs the question in both scenarios do I bound the form to the temprorary table? If so how?

 

[theDBguy]

OK I'm getting stuck here.
I've set up a mock form where I create a temporary table with selected data (only use certain fields and also have a filter) from another table. I do this in the open event. I try and make this the record set using
Set db = CurrentDb
Set rs = db.OpenRecordset("TEMPTABLE")
Set Me.Recordset = rs
but get error 7965 as at this moment in time the TEMPTABLEe doesn't exist. Moved code to the current and then the load event but still the same. I can see that the table doesn't exists. If I by pass the above 3 lines table appears.
How do I get around this. I could set the table up in the form that opens this one I suppose.
That begs the question in both scenarios do I bound the form to the temprorary table? If so how?

Hi. How exactly are you creating the temp table? I thought you would just bind the form to it. Otherwise, you could try using a timer event to bind your form to the temp table. Hope it helps...

 

[ryetee]

Hi. How exactly are you creating the temp table? I thought you would just bind the form to it. Otherwise, you could try using a timer event to bind your form to the temp table. Hope it helps...

using select into..


strTable = "itempricegroupTEMP"
'Delete the table if it exists
DoCmd.DeleteObject acTable, strTable

strSQL = "Select ID, [price group id], [item id], description, sortreference, units, cost, gross, disc INTO " & strTable & " FROM itempricegroup " & _
"Where [price group id] = 2"
DBEngine.BeginTrans
CurrentDb.Execute strSQL
DBEngine.CommitTrans
the begintrans and committrans don't work

are you saying my "temp table" should be a permanent table in each front end? so I delete all records and hen append the ones I want into it?
What I've got above creates the table I want but it only becomes "visible" after the open and load events