Append query / Combo box woes

  • Thread starter Thread starter Geoff
  • Start date Start date
G

Geoff

Hi everyone,

I'm trying to populate a combo box in my form automatically based on
whatever data is currently displayed in my subform. I've written some code
that will loop through all the subform records and store the field in a
variable, then I've been wondering what's best to do with that data. It's a
date field so that gave me some problems with the variable but I won't get
into details of that.

I've been researching this and the general view is that it's best to update
a table with the data, then have my combo based on that. So I've been trying
to find code that will update a table for me and although I have found some,
it doesn't make a lot of sense to me and seems sooo complex considering I
only want to add a few records to a table.

Anyway, I had a thought that maybe I could use an Append Query instead? So
after all that waffle above, my main question is this: Is there a way to
have an append query automatically update a table with one field taken from
only the records currently displayed in my subform?

Thanks loads,
Geoff.
 
Hi Geoff,

Yes, you could use an append query to do that.
First, you would use a delete query to clear the table.
Then, in the loop, instead of building your string you could create a query
string by concatenating each field value into a query string and use
DoCmd.RunQuery to run it.
You would need to turn off warnings before each append and turn it back on
after each append. You can use DoCmd.SetWarnings False to turn them off and
DoCmd.SetWarnings True.
Otherwise Access would put up a dialog "You are about to append 1 row
......blabla" etc.
Instead of using an append query, you open a recordset on your table right
after clearing it.
Then you can use the Add method to add each record and the Update to store
the data in the table. When all records have been added you close the
recordset.
A query is generally faster, but concatenating strings also takes time, so I
would not bet on which would be faster. The advantage of a recordset is that
it is easier to debug, although in this case hat might not matter.

Ragnar
 
Ragnar Midtskogen said:
Hi Geoff,

Yes, you could use an append query to do that.
First, you would use a delete query to clear the table.
Then, in the loop, instead of building your string you could create a query
string by concatenating each field value into a query string and use
DoCmd.RunQuery to run it.
You would need to turn off warnings before each append and turn it back on
after each append. You can use DoCmd.SetWarnings False to turn them off and
DoCmd.SetWarnings True.
Otherwise Access would put up a dialog "You are about to append 1 row
.....blabla" etc.
Instead of using an append query, you open a recordset on your table right
after clearing it.
Then you can use the Add method to add each record and the Update to store
the data in the table. When all records have been added you close the
recordset.
A query is generally faster, but concatenating strings also takes time, so I
would not bet on which would be faster. The advantage of a recordset is that
it is easier to debug, although in this case hat might not matter.
Click to expand...


Hi Ragnar,

Thanks very much, that all makes good sense. Since asking the question I've
managed (just a few minutes ago actually) to get an Append Query to select
the records I want without me having to do any other code. I didn't realise
I'd be able to do that just by entering something in the criteria. :) I
need to add code after that too though so I'll run the query in code as you
suggest, letting it select the data as I've set in the criteria, then I'll
requery the combo and hopefully it'll all work.

Thanks for the tips and examples of what code to use for dealing with the
warnings etc, that's all very useful and saves me having to look it all up.
:)

Cheers,
Geoff.
 
Back
Top