highest value in a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a database with 2 tables, A & B
table A has fields called booking number, flag and invoice number.
Table B contains 1 autonumber field called next number
the field booking number in table A is autonumber.
i want to be able to enter a record in table A and if field flag is ticked
field invoice number looksup the highest number in table B field next number
then increments table B field next number by 1.

can anyone help with this please or suggest a way to make this happen.

thanks
faffy
 
Faffy,

This will be difficult to do with an AutoNumber field in Table B. I
would suggest just making it a number field, and if I understand you
correctly, just leave it as one record.

You could make a macro using the SetValue action...
Item: [invoice number]
Expression: DLookup("[next number]","Table B")
.... followed by an OpenQuery action, to run an Update Query, which will
update the next number field in Table B to...
[next number]+1

Then, you can assign this macro on the After Update event property of
the Flag checkbox on your form.
 
thanks very much steve, i will try this approach

Steve Schapel said:
Faffy,

This will be difficult to do with an AutoNumber field in Table B. I
would suggest just making it a number field, and if I understand you
correctly, just leave it as one record.

You could make a macro using the SetValue action...
Item: [invoice number]
Expression: DLookup("[next number]","Table B")
.... followed by an OpenQuery action, to run an Update Query, which will
update the next number field in Table B to...
[next number]+1

Then, you can assign this macro on the After Update event property of
the Flag checkbox on your form.

--
Steve Schapel, Microsoft Access MVP

i have a database with 2 tables, A & B
table A has fields called booking number, flag and invoice number.
Table B contains 1 autonumber field called next number
the field booking number in table A is autonumber.
i want to be able to enter a record in table A and if field flag is ticked
field invoice number looksup the highest number in table B field next number
then increments table B field next number by 1.

can anyone help with this please or suggest a way to make this happen.

thanks
faffy
Click to expand...
Click to expand...
 
Back
Top