New Customer form

[elfranzen]

Ok I am making a form that an employee will scan a card and the number on the card will go into a text field once the number is entered. I need it to look it up in a table and see if that number is in there and if so and the customer information to another table. If not then open a new form and have the employee enter the customer information. I am just having trouble on how to look up the table to see if the number is already in there. I bet this is very easy but I just can’t think today. Can someone point me in the right direction?

 

[pbaldy]

The simplest is probably a DCount using the value from the form. More info on syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[elfranzen]

OK looked up Dlookup but can't see to think on how to get it to work. here are the steps I would like for this form to run.

1. Form is open and waiting for a cards to be scanned
2. Employee scans card
3. customer not in "customer" table pops up window please enter in data
4. After data is in pop up closes and that data is put in another table "customer here" as the customer is here


3b. If customer is already in Table "customer" just adds customer to table "customer here" that the customer is here.

so I have one table that lists all the customer information and if the then I want to move this data over to another table to track the customer being here.

 

[pbaldy]

If DCount(...) = 0 Then
  'Open form in dialog mode and enter customer info
End If

add the customer to "customer here"

 

[elfranzen]

Wow, Dcount not Dlookup man I feel like a tard now.

 

[pbaldy]

You can use DLookup, but then you have to test for Null, which is what it will return if the value isn't found. DCount is simpler.

 

[elfranzen]

If DCount("[firstname]", "[cardnametable]", "[CardNumber]'" = CardNumberGlobal) = 0 Then
DoCmd.OpenForm "Newcardform", acNormal, , , acFormAdd, acWindowNormal

Ok this is what I have and it is not working "firstname" not sure what this first expr is for
"Cardnametable" is the table I want to check for records
"Cardnumber" is the field I want to check againt the string "cardnumberglobal" what is this not working

 

[pbaldy]

What is the data type of CardNumber in the table? You've mixed the numeric and text methods, with the single quote before and none after.

 

[elfranzen]

If DCount("[cardnumber]", "cardnametable", "[CardNumber] = '" & CardNumberGlobal & "'") = 0 Then

Got it to work it was a string

 

[elfranzen]

If DLookup("[startplaying]", "tournamentlogtable", "[cardnumber] = '" & CardNumberGlobal & "'" & " and " & "[tournamentdate] = Date) = True Then

Ok now who would like get this code to work. I have everying working until I wanted to only return records with todays date. do I need to creat a where statement first?

 

[pbaldy]

Try

If DLookup("[startplaying]", "tournamentlogtable", "[cardnumber] = '" & CardNumberGlobal & "' and [tournamentdate] = #" & Date() & "#")

That would return True?

 

[elfranzen]

Thanks this worked