loop problem

[antonyx]

ok have a look at this..im trying to create an invoice number using the account reference.. (from a drop down) and a random 5 digit number..

i want the form to check the invoice table (invoiceref field).. to make sure that invoice number doesnt already exist.

Private Sub btnGenerate_Click()
Dim rdm As Long
Dim xlook As Variant

Do
[b]xlook = DLookup("InvoiceRef", "tblInvoice", "Criteria = '" & Forms!frmGenerateInvoice!txtinvoicenotest & "'")[/b]
rdm = Int((99999 - 11111 + 1) * Rnd + 11111)
Me.txtinvoicenotest = Me.cboAccountRef & rdm
Loop Until xlook <> Me.txtinvoicenotest
Me.txtinvoiceno = Me.txtinvoicenotest
End Sub

error 2001 on the above line. you cancelled the previous operation..

what am i doing wrong?

 

[AlienRay]

Try putting the field names in brackets:
xlook = DLookup("[InvoiceRef]", "tblInvoice", "[Criteria] = '" & Forms!frmGenerateInvoice!txtinvoicenotest & "'")

I'm assuming that "Criteria" is in fact the name of a field in the tblInvoice table.

 

[KenHigg]

That is some pretty creative vba...

Is 'Criteria' really a field in tblInvoice?

 

[antonyx]

...ermm. criteria is not a field..

i must have used another example.. and left that there

what should go in the [] then because i have already specified the fieldname i want to search through..which is InvoiceRef

 

[KenHigg]

In the example you posted, the dlookup function will return whatever is in the field 'InvoiceRef'. As a rule of thumb the square brackets can be used wherever a field name or table name may contain spaces. I think they may be required in the dlookup function. I know I have always used them there...

Try getting the dlookup to work with something real simple like maybe a msgbox so you can get the hang of it first...

 

[antonyx]

ok.. i think im on to something.. hold on.

 

[AlienRay]

The problem now is that your Criteria and your result are the same thing: the InvoiceRef field. Do you really need to look up the Invoice Reference, or are you getting that value from the frmGenerateInvoice form?

Just out of curiosity: is there a reason you want to return a random five digit number, rather than simply using sequential numbers?

 

[antonyx]

Just out of curiosity: is there a reason you want to return a random five digit number, rather than simply using sequential numbers?

it would suit my requirements if the number is random in this way yes

this loop did nothin useful..

it froze access infact..

in my mind.. im creating an invoice number using the account ref from my combo.. and a random 5 digit number.. i am placing that no in a temp textbox on my form called invoicenotest..

i am then searching the records in the invoice table.. invoice ref field (pk).. and comparing what is in those fields with what has just been placed in the invoicetestno textbox..

and this task must loop until the value in the invoicetestno textbox is different to all the invoiceref fields in the invoice table..
when it is different.. place that temp value in the InvoiceNo control.. which will make a new record..

is this the wrong approach??

Private Sub btnGenerate_Click()
Dim rdm As Long
Dim xlook As Variant

Do
rdm = Int((99999 - 11111 + 1) * Rnd + 11111)
Me.txtinvoicenotest = Me.cboAccountRef & rdm
xlook = DLookup("[InvoiceRef]", "tblInvoice", "[InvoiceRef] = '" & Forms!frmGenerateInvoice!txtinvoicenotest & "'")
Loop Until xlook <> Me.txtinvoicenotest
Me.txtInvoiceNo = Me.txtinvoicenotest
End Sub

 

[antonyx]

just to give you a better idea of what i am doing.. here is the form..

http://www.londonheathrowcars.com/hereitis.jpg

you choose an account holder.. and the 3 letter ref is used for that account holder.. eg Mcdonalds is MCD

so a ref no would be

MCD76885

 

[KenHigg]

Sorry - I need to head to the house - I think you need to do something like:

if isnull(dllookup(etc...))
use the random number
else loop back through again

Hope this helps -

Ken

 

[antonyx]

AlienRay.. do you have any further thoughts on this matter??

ill try to code how ken told me.. but i am not optimistic.

 

[KernelK]

Try to use: Loop Until Not IsNull(xlook)

The reason being is that dlookup is going to return Null anytime the Ref number is not in the table. I think comparing a Null variable with a number is the problem, because it returns Null itself. So you just want to check if xlook as a value or not, not if the value is equal to what it is in the textbox.

 

[antonyx]

ok thanks.. ill give it a go

 

[antonyx]

this doesnt work..just freezes access.. no error comes up

is it in the wrong order?

Private Sub btnGenerate_Click()
Dim rdm As Long
Dim xlook As Variant

Do
rdm = Int((99999 - 11111 + 1) * Rnd + 11111)
Me.txtinvoicenotest = Me.cboAccountRef & rdm
xlook = DLookup("[InvoiceRef]", "tblInvoice", "[InvoiceRef] = '" & Forms!frmGenerateInvoice!txtinvoicenotest & "'")
Loop Until Not IsNull(xlook)
Me.txtInvoiceNo = Me.txtinvoicenotest
End Sub

 

[antonyx]

i removed the Not and it seems to be working

 

[antonyx]

if xlook is goin to be null when there are no values found that match the current generated invoice number..

i want the new invoice number to stay put when xlook is null..

so the not shouldnt be there right?