How to validate against another table?

[rjohnson@Madison.k12]

I have an Inventory Table listing all Desktops, Laptops, Netbooks.
I have a Valid Computer Models table.
I have a Form they use to add & change Computers in Inventory.
The form has multiple Comboboxes that norrow the Choices of Manufacturer, Make and Model depending on the Item Type of Desktop, Laptop & Netbook.

My problem exists at the end when they are all done entering data, I need to validate what they have entered against the Valid Computer Models.

What code can I use that to check if they have entered a valid Computer Model? I know it can go in the Before_Update event of the form because other edits are working fine there. The key fields are: Item Type, Manufacturer, Make & Model. Thanks Rick

 

[dkinley]

Greetings and welcome to the forums!

You could use a recordset and loop through performing comparisons. However, why not make those items a combo-box on the form?

In this manner, the user selects certain items (manu, model) from the combo box thus negating the need to validate after input since the combo box performs the integrity and validation prior?

-dK

 

[rjohnson@Madison.k12]

I have made them as comboboxes, but I cannot force them to fill every combo box in sequence. If they choose to skip a box or they go back and change the Make, I can't force them to recheck the Model.
How would I check to see if the record exists? (NoMatch, Dlookup, Findfirst)? Thanks Rick

 

[dkinley]

Ah I see. In the BeforeUpdate event of the form, you could use ...

If Len(Nz(Me.cboComboManu, "")) = 0 Then
        MsgBox "You must enter the manufacturer.", , "Manufacturer"
        Me.cboComboManu.SetFocus
ElseIf Len(Nz(Me.cboComboMake, "")) = 0 Then
        MsgBox "You must enter the make.", , "Make"
        Me.cboComboMake.SetFocus
ElseIf ....
End If

You could use the AfterUpdate event of the combo box to reset the other combo boxes (those that follow the logic chain) ....

'once manufacturer is selected
Me.cboComboModel.Requery

HTH,
-dK

 

[rjohnson@Madison.k12]

Thanks for these quick responses. I have been stuggling with this question for about a week.

I already do a requery to rebuild the comboboxes. That works fine. I just need to validate at the end of the Form. Sometimes data gets uploaded into the Inventory Database manually and I need to Validate the Form if they ever chage the Record. What code will validate to another table to see if record exists?
Thanks again, Rick

 

[dkinley]

Gotcha.

I would assume that you would validate against a unique field such as a serial number? This link can provide some insight.

Also, I forgot to add a Cancel = True in each If event for the form validation. This will cancel the save action on the event. On the evaluation of the duplicate, (in another If statement) you would need to utilize the Cancel = True for a duplicate.

-dK

 

[vbaInet]

Just adding to what dk said, Cancel in the BEFORE INSERT event for new records.

I would be more inclined to use the DCount() function instead of the DLookup() so you don't have to deal with NULLs. Set the criteria and check if it's Not Equal to 0.

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

 

[rjohnson@Madison.k12]

I have tried to get the syntax correct for Dcount() with multiple values but I keep getting compile errors.
DCount("Model","Valid Computers","[Item Type]= & ([Forms]![frmAssests]![Item_Type]) AND Manufacturer = ([Forms]![frmAssests]![Manufacturer]) AND Make = ([Forms]![frmAssests]![Make] AND (Model)=[Forms]![frmAssests]![Model])")
I have also tried using a string value as part of the 3rd argument, but I get the compile errors.
How do I check Dcount value when it does work?
Any thoughts?
Thanks Rick

 

[dkinley]

Almost there .... you are missing some quotes where you are trying to refer to the form ...

","[Item Type]= " & ([Forms]![frmAssests]![Item_Type]) & " AND

These will break up the string to pull in the value you are referring to. You will need to do the same wherever you try this at. Also, notice I used a single set of double quotes - this is for a number type (assuming the primary key is a number you stored it and not text). You will use a single then a double for string references ...

","[Item Type]= '" & ([Forms]![frmAssests]![Item_Type]) & "' AND

These can get all tricky especially when dealing with other data types and apostrophe's. If I can find the one page that has alot of this on it, I will post it.

-dK

 

[vbaInet]

How do I check Dcount value when it does work?

If DCount(...) <> 0 Then
     ... the record exists
else
     ... it doesn't exist
end if

 

[rjohnson@Madison.k12]

We've almost got it but I still get compile errors.
They are All character fields so I tried this: (I get Expected: = error)
DCount("Model","Valid Computers","[Item Type]= '" & ([Forms]![frmAssests]![Item_Type]) & "' AND ","[Manufacturer]= '" & ([Forms]![frmAssests]![Manufacturer]) & "' AND ","[Make]= '" & ([Forms]![frmAssests]![Make]) & "' AND ","[Model]= '" & ([Forms]![frmAssests]![Model]))')
So i tried this and I get an Expected end of statement error
DCount("Model","Valid Computers", "[Item Type]= " & ([Forms]![frmAssests]![Item_Type]) & " AND ","[Manufacturer]= " & ([Forms]![frmAssests]![Manufacturer]) & " AND ","[Make]= " & ([Forms]![frmAssests]![Make]) & " AND ","[Model]= " & ([Forms]![frmAssests]![Model])))
Can you take another look? Thanks, Rick

 

[dkinley]

I would suggest just trying one. Let that one work error free. Then add another and debug it, and then add another condition, etc. etc.


I would point you to this article. Scroll down to the 'multiple criteria' section.

HTH,
-dK

 

[vbaInet]

Here you go:

DCount("Model","[Valid Computers]","[Item Type]= '" & Nz([Forms]![frmAssests]![Item_Type]) & "' AND [Manufacturer]= '" & Nz([Forms]![frmAssests]![Manufacturer]) & "' AND [Make]= '" & Nz([Forms]![frmAssests]![Make]) & "' AND [Model]= '" & Nz([Forms]![frmAssests]![Model]) & "'")

 

[dkinley]

Ah .... here is something when dealing with apostrophes and quotes and such with strings. It also has another link (on DLookup) on it and both pages may provide some insight now and when moving forward with your project.

http://www.everythingaccess.com/tutorials.asp?ID=Quotation-marks-within-quotes

Keep in mind, they use DLookup but this will be the case when dealing with strings.

-dK

 

[rjohnson@Madison.k12]

Hi vbaINet
You stated try this and I still get a Compile Error stating "Expected ="
DCount("Model","[Valid Computers]","[Item Type]= '" & Nz([Forms]![frmAssests]![Item_Type]) & "' AND [Manufacturer]= '" & Nz([Forms]![frmAssests]![Manufacturer]) & "' AND [Make]= '" & Nz([Forms]![frmAssests]![Make]) & "' AND [Model]= '" & Nz([Forms]![frmAssests]![Model]) & "'")
Any further thoughts? (Access 2002?)
THanks Rick

 

[dkinley]

Are you using the arguments supplied by vbaINet?

That is, if you have DCount on a code line and nothing else, you will get that error. You have to provide an argument for it ....

If DCount(allofyourstuff) <> 0 Then
... the record exists
else
... it doesn't exist
end if

-dK

 

[vbaInet]

You have to equate the DCount to something, a variable perhaps:

Dim myVar as integer
myVar = DCount(...)

OR

You perform the check on the DCount using the aircode in post #10

 

[rjohnson@Madison.k12]

THANKS Dkinley and vbaINET.
I used the NZ code to force them to enter a value.
Then I used the Dcount to a variable to validate against the table.
Here is my final code.
THANKS AGAIN!!! Rick

' Validate Data based on Valid Computers Table
If Me![Item Type] = "Desktop" Or Me![Item Type] = "Laptop" Or Me![Item Type] = "Netbook" Or Me![Item Type] = "Printer" Or Me![Item Type] = "Laptop" Or Me![Item Type] = "Projector" Or Me![Item Type] = "Document Camera" Then
If Len(Nz(Me!Manufacturer, "")) = 0 Then
MsgBox "You must enter the Manufacturer.", , "Manufacturer"
Cancel = True
Exit Sub
ElseIf Len(Nz(Me!Make, "")) = 0 Then
MsgBox "You must enter the Make.", , "Make"
Cancel = True
Exit Sub
ElseIf Len(Nz(Me!Model, "")) = 0 Then
MsgBox "You must enter the Model.", , "Model"
Cancel = True
Exit Sub
End If
Dim MyVar As Integer
MyVar = DCount("Model", "[Valid Computers]", "[Item Type]= '" & Nz([Forms]![frmAssests]![Item_Type]) & "' AND [Manufacturer]= '" & Nz([Forms]![frmAssests]![Manufacturer]) & "' AND [Make]= '" & Nz([Forms]![frmAssests]![Make]) & "' AND [Model]= '" & Nz([Forms]![frmAssests]![Model]) & "'")
If MyVar = 0 Then
MsgBox "This is not a Valid Computer Manufacturer, Make & Model. Please check the Dropdown."
Cancel = True
End If

 

[dkinley]

Awesome .... glad it is working for you!

Good luck!

-dK

 

[vbaInet]

Excellente. Good job!!!!!

By the way, I'm not sure about your criteria --> If myVar = 0

I thought you're supposed to be prompting if that record exists?