check if multiple values exsist (1 Viewer)

[cocowomble]

Hi,

I am relatively new to access and Vba, and seem to be making good progress, I am making an asset table which amongst other fields have fields for "serialNo" and "Manufacturer"

I am trying to write some code that after update of manufacturer in the form, will check to see if that serialNo and Manufacturer exsist.

ive managed to do it for one value, using

If Not IsNull(DLookup("[serialno]", "Assets", "[serialno] = '" & Me!serialno & "'")) Then
msgbox "blah blah"

which works great, but am struggling when i'm asking it to lookup two values.

any help would be greatly appreciated.

Thanks

 

[Galaxiom]

Use AND between the conditions.

 

[Adam McReynolds]

I imagine you are having the same trouble I did. The double quote from the begining of the "Where" condition does not close the first criteria but on the second, as can be seen that the second criteria here does not begin with the double quote but ends with it, thus closing both condition into one. Hope this works for you, if not there are many smart people here that will help you.

If Not IsNull(Dlookup("[serialno]", "Assets", "[serialno] = '" & Me!serialno & "' And [Field2 Here] =  'String Here or Change to Variable'"))Then
msgbox "blah blah"

 

[Adam McReynolds]

I imagine you are having the same trouble I did. The double quote from the begining of the "Where" condition does not close the first criteria but on the second, as can be seen that the second criteria here does not begin with the double quote but ends with it, thus closing both condition into one. Hope this works for you, if not there are many smart people here that will help you.

If Not IsNull(Dlookup("[serialno]", "Assets", "[serialno] = '" & Me!serialno & "' And [Field2 Here] =  'String Here or Change to Variable'"))Then
msgbox "blah blah"

Ooops. I think this is what you where looking for.

If Not IsNull(Dlookup("[serialno]", "Assets", "[serialno] = '" & Me!serialno & "'")) And Not IsNull(Dlookup("[Field2]", "TBL2", "[serialno] = '" & Me!serialno & "'"))Then
msgbox "blah blah"

 

[cocowomble]

excellent, tried this before but missed out the second 'not'

thanks adam

 

[Galaxiom]

Ooops. I think this is what you where looking for.

If Not IsNull(Dlookup("[serialno]", "Assets", "[serialno] = '" & Me!serialno & "'")) And Not IsNull(Dlookup("[Field2]", "TBL2", "[serialno] = '" & Me!serialno & "'"))Then
msgbox "blah blah"

However note that for checking two fields in the same table it would be quite inefficient to use two DLookups like that.

 

[cocowomble]

Galaxiom, what would you suggest instead of using dlookup?!

 

[Galaxiom]

The Dlookup is fine. The only point I was making is that if the values being tested are from two fields in the same table then they should be combined in the conditions of a single DLookup.

 

[cocowomble]

That's what I thought and tried initially, but just couldn't seem to get the code right.

 

[Galaxiom]

As Adam originally said, it is a matter of getting the quote marks in the right place. He just didn't have enough information to go on.

Assuming text for both fields this uses a DCOunt which is all that is required if you just need to test for the presence of a record.

DCount("*", "Assets", "[serialno] = '" & Me!serialno & "' And [manufacturer] ='" & Me!manufacturer & "'")