Sequential Number Dlookup

[connerlowen]

Hi,

I have a form that generates a sequential number called the "QuoatationNumber". I would like this QuotationNumber to appear in a text box at the top of all of the following forms until the quotation is finished. I have tried DLookUp, but it only gets the first quotationnumber and does not get the next number when a new quotation is started.

Thanks,

Conner Owen

 

[Ranman256]

build a query that pulls the Q#....This query is the recordsource of your combo.
when a new record is created , via save button, also include a: cboQ.requery
to update the list.

 

[connerlowen]

I have a Query. That is where the DLookUp function is looking. It is now saying that the database cannot find the "QuotationNumber" field in the expression.

 

[pbaldy]

Discussion continued here; please don't post the same question twice.

 

[connerlowen]

This is a different question. The other thread deals with the error message. This thread deals with text boxes within other forms.

 

[vbaInet]

I think Conner is after a DMax()?

 

[connerlowen]

How do I use the DMax function within the DLookUp function? my function as of right now says =DLookUp("QuotationNumber","NewQuotationQ") that is the field within the query I created.

 

[vbaInet]

Here's some information on DMax()

http://www.techonthenet.com/access/functions/domain/dmax.php

 

[namliam]

How about simply passing the Number along the forms using the open args of DoCmd.openform and/or using the "On Open" of the form to pick it from the previous form?

 

[connerlowen]

Thank you to All that helped. I got it to work.

 

[vbaInet]

What did you end up doing?

 

[connerlowen]

Control Source
=DLookUp("QuotationNumber","NewQuotationQ","[QuotationID]=" & DMax("[QuotationID]","[NewQuotationQ]"))

 

[vbaInet]

Fyi, you could also achieve the same thing by:
* creating a query based on NewQuotationQ
* include only the QuotationID and QuotationNumber fields
* sort QuotationID in Descending order - crucial part of this process
* save the query and...
* use only the DLookup() to grab the QuotationNumber

=DLookup("QuotationNumber", "[COLOR="Blue"]NewQueryHere[/COLOR]")

 

[connerlowen]

The NewQuotationQ is a query

 

[vbaInet]

* creating a query based on NewQuotationQ

A query of a query.

 

[connerlowen]

Oh I see now.

 

[namliam]

Ugh, each form doing a dlookup

Instead just pass it along using the openargs, saves a lot of I/O and thus a lot of time...