restict data input to text field <> to query result 2

[installer69]

I need to prevent the input of serial numbers in to a text box if they appear in a query called duplicates which lists all stock currently in the warehouse.

I cannot use a simple no duplicates in the table because we sometimes return items to our supplier and have them re-issued to us at a later date.

Help please

 

[RickSpr]

In a form, use a DLookup() function in the text box's BeforeUpdate event procedure to access the Duplicates query for a matching serial number. If DLookup() returns Null, simply exit the procedure. Otherwise, it's a duplicate, so show a MsgBox and then set the procedure's Cancel parameter to True. This will keep the cursor in the text box until the problem is corrected.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[lupins46]

In the afterupodate event procedure of the textbox put:

if dcount("*","duplicates","serialnumberfieldname = " & me.serialnumbertextboxname)>0 then
msgbox "Duplicate"
me.serialnumbertextboxname = ""
else
end if

Use your own field and textbox names.

 

[RickSpr]

Lupins46:
If you try to change a control's value within its AfterUpdate event, Access messes up (though I forget exactly how). However, if you change the line [red]me.serialnumbertextboxname = ""[/red] to [green]Cancel = True[/green] and put this code in the BeforeUpdate event, it will do what installer69 wants.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[installer69]

Damn, you guys are good. I used the Rick method and all was good.

It's just that I forgot to mention one thing.....the serial field is not a number! (I changed the setting on my work in progress version to 'number' to try this out) The stupid things have letters in as well so I have them saved as text. How do I modify this procedure to compensate?

I will be experimenting while I wait for your excellent reply to prove I'm not a complete lamer. Oh well, nearly 4am again.

 

[installer69]

Okay, for the benefit of anyone else who may find this useful: To use the above with a text field change

if dcount("*","duplicates","serialnumberfieldname = " & me.serialnumbertextboxname)>0 then
msgbox "Duplicate"
Cancel = True
else
end if

to

If DLookup
**all on same line to work**("serialnumberfieldname", "duplicates", "serialnumberfieldname = " & me.serialnumbertextboxname)>0
**all on same line to work**
thenMsgBox "Duplicate"
Cancel = True
Else
End If

 

[installer69]

Just a final question please. How do I clear the text box ready for next entry (we use a barcode scanner) after the duplicate text box has been clicked and we have been sent back to the text box. It is a pain to have to highlight the previous duplicate entry first and then delete it.

do I need to set Me.serialnumbertextboxname to "" somewhere prior to bouncing back to it?

 

[installer69]

Oh I'm stupid.

If DLookup
**all on same line to work**("serialnumberfieldname", "duplicates", "serialnumberfieldname = " & me.serialnumbertextboxname)>0
**all on same line to work**
thenMsgBox "Duplicate"
Cancel = True
Else
End If

Didn't work. i had just forgotten to change the field serial back to text. help please

 

[installer69]

I think I have it but any advice welcome:

**all on 1 line**
If DLookup("serial", "duplicate_query", "serial ='" & Me.Serial & "'") = Serial Then
**all on 1 line**
MsgBox "Duplicate"
Cancel = True
Else
End If

still cant get it to reset text box ready for next entry after a duplicate

 

[RickSpr]

Sorry not to respond. I was out all afternoon and evening.

Your last effort (using apostrophes) was correct, as you apparently found out. Good work!

About clearing the text box: That you can't do. And I'm not sure you'd really want to. It's kind of rude to tell the user that what they entered was wrong, and then blank it out so they can't see what their mistake was. For this particular purpose, it would have suited you, but in general it wouldn't be a good idea.

The closest you can probably get--and I'm not sure even this will work--is to select the contents for them so they can just type over it (the first keystroke will replace the whole value). You would do that with:
Me.Serial.SelStart = 0
Me.Serial.SelLength = Len(Me.Serial)
(Hope I got that right. I should look it up but I'm too tired.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[installer69]

Rick, Sir, you are a false idol that I would most certainly worship. That last one did what I meant not what I asked for.

I cannot praise enough the efforts of people like yourself who give time to people like me whose main career is nothing to do with computers.

I am the MD of a satellite contractor and build tailor made solutions to my data needs because I cannot find off the shelf progs that do what I want.

I have learnt everything I know about access, MySql and PHP on a 'need to solve a problem' basis and with the help of sites and people like this/you. (and many hours of writing code that does nothing, ammending it, repeat 'till solved)

I have one more question on this form but I need to think about the right way to ask it so that it makes sense

Thanks again Rick,

Craig