easier way to pupulate form

[theDBguy]

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

Hi. Either way works. It doesn't make much difference, really. So, if the current issue is a timing problem, then you could try the Event Timer suggestion I mentioned earlier and let us know if it doesn't help.

 

[ryetee]

Hi. Either way works. It doesn't make much difference, really. So, if the current issue is a timing problem, then you could try the Event Timer suggestion I mentioned earlier and let us know if it doesn't help.

OK being a bit thick here. I thought the form timer event ran the timer event after a defined number of milliseconds. How does that help and what do I put in the time event. Or are you suggesting that after creating the table I somehow "wait" fora number of seconds before code continues? Again how do I do that?

 

[theDBguy]

OK being a bit thick here. I thought the form timer event ran the timer event after a defined number of milliseconds. How does that help and what do I put in the time event. Or are you suggesting that after creating the table I somehow "wait" fora number of seconds before code continues? Again how do I do that?

Hi. No worries. You are actually closer than you think. Yes, I am suggesting let the form open/load as usual; but then, after a "short" delay, using the Timer event, you can then try to set the recordset to the temp table, which hopefully will be available after waiting a little bit of time. So, to try it out, remove the code from the Open or Load event to set the RecordSource to the Timer event. To trigger it, set the Timer interval to however long you want to wait. You can experiment until you find a good amount. In your Timer event code, the first thing you have to do is set the Timer Interval value to zero (0). This will turn off the timer event, so it will only fire once when the form opens. Hope it makes sense...

 

[ryetee]

Hi. No worries. You are actually closer than you think. Yes, I am suggesting let the form open/load as usual; but then, after a "short" delay, using the Timer event, you can then try to set the recordset to the temp table, which hopefully will be available after waiting a little bit of time. So, to try it out, remove the code from the Open or Load event to set the RecordSource to the Timer event. To trigger it, set the Timer interval to however long you want to wait. You can experiment until you find a good amount. In your Timer event code, the first thing you have to do is set the Timer Interval value to zero (0). This will turn off the timer event, so it will only fire once when the form opens. Hope it makes sense...

I played around with this and found although my table wasn't actually appearing down the left hand side it was actually there!
So now I have a temporary table that the user can tick what items he wants and he can change the number as well. When he closes the form where he does this I want to (in the code) populate the blank (continuous) form with his entries.

So basically
1. OPen the database table to read the records/rows
2. Read rows until end of file
3. If row not checked then got to 2
4. Move item number and description and number to next available space on form
5. go to 2

I'm pretty sure I know what to do for reading the database but no idea how to transfer to the form.

 

[theDBguy]

I played around with this and found although my table wasn't actually appearing down the left hand side it was actually there!
So now I have a temporary table that the user can tick what items he wants and he can change the number as well. When he closes the form where he does this I want to (in the code) populate the blank (continuous) form with his entries.

So basically
1. OPen the database table to read the records/rows
2. Read rows until end of file
3. If row not checked then got to 2
4. Move item number and description and number to next available space on form
5. go to 2

I'm pretty sure I know what to do for reading the database but no idea how to transfer to the form.

It might help to think of it this way. The form gets the data from the table, so to transfer the selected data to the form, you simply add it to the table and refresh the form. Correct? Also, there's no need to use a loop for this, basically eliminating steps 2 and 3. Just use a query with a criteria to only retrieve the checked/selected rows.

 

[ryetee]

It might help to think of it this way. The form gets the data from the table, so to transfer the selected data to the form, you simply add it to the table and refresh the form. Correct? Also, there's no need to use a loop for this, basically eliminating steps 2 and 3. Just use a query with a criteria to only retrieve the checked/selected rows.

I know but the form already exists and there's a lot of validation in there that may be messy to move. The same form is also going to be used to add extra items in as well. I was hoping to add each item to the form and (maybe naive thinking this) the before and after updates would get processed.

If not I need to basically start from scratch.

 

[theDBguy]

I know but the form already exists and there's a lot of validation in there that may be messy to move. The same form is also going to be used to add extra items in as well. I was hoping to add each item to the form and (maybe naive thinking this) the before and after updates would get processed.

If not I need to basically start from scratch.

Again, when you say you want to "add" to a form, if it is bound, then you are adding to the table (record source). To execute the Before/After Update events, which don't fire when you modify data using code, then you'll have to explicitly call them. If you are calling those events from a different form or code module, then you'll have to declare them Public first.


PS. You know, this could have been easier if you could share a sample copy of what you're working with, so we can give you more specific suggestions.

 

[ryetee]

Again, when you say you want to "add" to a form, if it is bound, then you are adding to the table (record source). To execute the Before/After Update events, which don't fire when you modify data using code, then you'll have to explicitly call them. If you are calling those events from a different form or code module, then you'll have to declare them Public first.


PS. You know, this could have been easier if you could share a sample copy of what you're working with, so we can give you more specific suggestions.

Not always possible to share. I have to usually cut it down to the form and that takes time I don't have at the moment!
I'll have to do some rework. It's no big deal just thought I could get away without doing it!

 

[theDBguy]

Not always possible to share. I have to usually cut it down to the form and that takes time I don't have at the moment!
I'll have to do some rework. It's no big deal just thought I could get away without doing it!

Unfortunately, I couldn't tell you for sure if you could get away with it or not without seeing it first. By "sharing," I'll be happy with simply "watching" you show it to me. For example, using a screen sharing app like TeamViewer or Zoom. I don't need to have a physical copy of the db.

 

[ryetee]

Unfortunately, I couldn't tell you for sure if you could get away with it or not without seeing it first. By "sharing," I'll be happy with simply "watching" you show it to me. For example, using a screen sharing app like TeamViewer or Zoom. I don't need to have a physical copy of the db.

OK I've got this to work. Whether or not I've transgressed any unwritten things you shouldn't do but...

Before I start this form works using a combo box to select new items but the user wants a bulk update.
I create a temp table in a pop up form which the user can select which records he wants
In the main form I read through the temp table and for each row selected I set
me.itemid = to that of the read record. I do this as the before update event on the form oes various things with the item id. I then call itemid_beforeupdate. It needs an integer parameter so I call it with itemid_beforeupdate(0).
To move to the next 'available' space on the form I use DoCmd.RunCommand acCmdRecordsGoToNext.
All seems to work fine but am I on a sticky wicket!!!!?

 

[theDBguy]

OK I've got this to work. Whether or not I've transgressed any unwritten things you shouldn't do but...

Before I start this form works using a combo box to select new items but the user wants a bulk update.
I create a temp table in a pop up form which the user can select which records he wants
In the main form I read through the temp table and for each row selected I set
me.itemid = to that of the read record. I do this as the before update event on the form oes various things with the item id. I then call itemid_beforeupdate. It needs an integer parameter so I call it with itemid_beforeupdate(0).
To move to the next 'available' space on the form I use DoCmd.RunCommand acCmdRecordsGoToNext.
All seems to work fine but am I on a sticky wicket!!!!?

Hi. Glad to hear you got something going. There should be no need (at least I don't think so) to walk through the records one at a time, but if it works, it's good enough. I'm sure you'll be fine for now or find a better way later on. Good luck with your project.