MS Access/Query/VBA question

[flynn]

I have this table layout:


JobID (text field)
NextOrderNumber (Number field)


The table contains this data

JOBID NEXTORDERNUMBER
===========================
BL11 1
BL11 2
BL34 1
BL11 3


I have a query which will return the maximum of the NextOrderNumber field based on the JobID. For example, my query searches all JOBID's matching "BL11" and returns the number "3" (which is the maximum of all JobIDs).

What I need to know is how to assign the value from the query to a VBA variable which will then be written to the table. Example: since my query returned the number "3", I will increment it to "4", then create a new JOBID record (BL11) with the NEXTORDERNUMBER as "4".

Hopefully this question makes sense. My question, then is how can a person assign the results of a query to a variable in VB?

I am wanting to do something similar to this:
myvar = query.max_order_number + 1

Thanks in advance

flynn

[ralph]

Queries that return a result are captured in a recordset (assumes the query was called with a recordset object (ado or dao)).
For one result, it's a one column recordset with one value so:
myvar = rs(0).Value + 1

[Ephesians]

Originally posted by ralph
Queries that return a result are captured in a recordset (assumes the query was called with a recordset object (ado or dao)).
For one result, it's a one column recordset with one value so:
myvar = rs(0).Value + 1

That's a TWO column table with two values. You'll want to change that to myvar = rs(1).value +1 but there's nothing wrong with using the name of the field to see things more clearly. eg:

MyVar = RS!MaxofNextOrderNumber + 1 
 
'but you also wanted to know how to write it back, right?
 
MyJobID = RS!JOBID
 
with RS
     .AddNew
     !JOBID = MyJobID
     !NextOrderNumber = MyVar
     .Update
end with

Try that...

[ralph]

That's a TWO column table with two values.

So, how do you know the query returns two columns? You don't know that or the name without seeing the sql.

[Ephesians]

Because he wants to be able to write the information back to the table. It wouldn't make sense to have two Recsets for that. Better to have one recset with both columns. I do concede that I jumped the gun on that on though, Ralph. I've seen screwier design.