VBA: If it can't find the value, then it equals this

[nbowman56]

Hi all,
Brand new to VBA here, so I'm in need of some assistance!
I have a database where you input one number, it looks up the number from a table, then spits out another number associated in the record (like vlookup). For the first value, I used an unbound combo box that has an invisible column holding the second value.
This is the code I used for this:

Private Sub txtPOTSku_Click()
Me.txtPotSku = Me.cboStockSku.Column(1)
End Sub

Now I'd like to incorporate some code that spits out "N.O.L" for the second value if it can't find the first value in the table. I hope this makes sense!

Thanks in advance,
Nicole

 

[plog]

"N.O.L" for the second value if it can't find the first value in the table

In a lot of cases coding is just translating english to the language you are using. You did a great job of using english to describe what you want, so the above is a perfect candidate for this. Check out these functions:

IIF - https://www.techonthenet.com/access/functions/advanced/iif.php

IsNull - https://www.techonthenet.com/access/functions/advanced/isnull.php

Give it a shot, then if you can't get it to work, post back here your full code and what exactly is occuring to make it "not work"

 

[nbowman56]

Thanks Plog,
I have attempted many variances of if/then type of statements to no avail.
I neglected to realize and indicate that besides the code listed above, I also have an After Update Macro associated with the first inputted value, which uses the SearchForRecord macro, where the Where condition is "="[Stock Sku] = " & "'" & [Screen].[ActiveControl] & "'""

I honestly just can't get the logic down for this one - I can't seem to construct a proper If Then statement with this code:
Private Sub txtPOTSku_Click()
Me.txtPotSku = Me.cboStockSku.Column(1)
End Sub

I've attempted things like...
Private Sub txtPOTSku_Click()
Me.txtPotSku = Me.cboStockSku.Column(1)
If IsNull(Me.txtPotSku) Then
Me.txtPotSku = "N.O.L"
End Sub

I know the above attempt isn't correct (and I will get an error with it), but how to better construct this? Also, let me know if more info is needed.
Thanks,
Nicole

 

[nbowman56]

Oh, and I have the control source for the second textbox (that spits out the associated value) set to "=[cboStockSku].[Column](1)"

 

[nbowman56]

Okay I worked out a bit more of the logic. I want it to go:
If user input into ComboValue is found in Table1, then TextValue = ComboValue(column1)
Else TextValue2 = "N.O.L"

 

[MajP]

'Not sure why you need the comb. Assuming the thing in txtPotSku is a string and not numeric

Private Sub txtPOTSku_Click()
     Me.txtPotSku = nz(dlookup("yourFieldNameToReturn","YourTableName","LookupFieldName = '" & me.TxtPotSku.text & "'"), "N.O.L")
End Sub

 

[Cronk]

I'd use the combo and set the row source to the table with a limit To list. That way the user cannot input a non existent value.

 

[nbowman56]

Hi Majp! Thank you for the code! I tried initially to use dlookup, but couldn't get it to work so that's why i settled on a combo box.
I tried out your code, and it almost works - but instead of returning the 2nd value if found in the table, it goes straight to showing "N.O.L". Would you know why this happens? I only want it to return NOL if it can't find the first value in the table

 

[nbowman56]

Shoot actually I just remembered that the logic was needing to be slightly altered than what I originally said. Sorry, the individual asking for this changed it up just minutes ago.
I would like my combo box (though i could change this to text) to look up the value inputted by user in a table, and if found, if it has a second value in the record, then output that value. If it does not have a second value associated to it, then output "N.O.L". If it can't find the value at all, then a message box should pop up.