Searching for record using multiple criteria (1 Viewer)

[Cirrostratus]

not sure if it will change anything but try removing the brackets in your DLookup:

Buildable = DLookup("UnitsOpen", "SQ_ProdUnitsBuildable", strWhere)

Good idea, but no change unfortunately..

I tried taking the RemoveFirstChar out from it entirely, but even without it, its not working. Could the Lot_Size > Buildable not be working? I'm not sure if that's even the correct way to use that? Should there be quotes or something around it?

Thanks for all the suggestions so far! I'm willing to try anything

 

[Cirrostratus]

If you had used the code I have in post#16 you should have atleast got a 0 in msgbox.. Could you copy the code as such and try again please..

No, I definitely gave that a shot. Tried again just to be sure. No message box pop's up. Its not generating anything..

 

[TJPoorman]

The problem with the null value is that your dlookup isn't returning anything. This probably means that your strWhere is not finding any records. Try putting a breakpoint on the dlookup and then hover over your strWhere on the line before to make sure that each variable has a value being pulled in.

 

[TJPoorman]

or better still....

debug.print strWhere on the line before your dlookup

 

[Cirrostratus]

Ok, I tried the debug but its not producing anything. I also tried the watch and hovering over strWhere (after plugging in the values in the form) and still nothing is coming up.

The program looks like this now:

Private Sub Lot_Size_AfterUpdate()
Dim strWhere As String, Buildable As Long
strWhere = "([Order No]='" & Me.Order_No & "') AND ([Release No]='" & Me.Release_No & "') AND ([Sequence No]='" & Me.Sequence_No & "')"
Debug.Print strWhere
Buildable = Nz(DLookup("[UnitsOpen]", "SQ_ProdUnitsBuildable", strWhere), 0)
MsgBox Buildable, vbInformation, ""
'Forms!frmProdStartEntry!

If Me.Lot_Size > Buildable Then
MsgBox "You can only build " & Buildable & "pcs, please revise qty!", vbInformation, ""
Cancel = True
Me.Lot_Size.SetFocus
Exit Sub
End If

btnStart.SetFocus

End Sub

I was able to reconfigure the query so I didn't need to do the RemoveFirstChar anymore. I also noticed a "small" mistake on my part on the field naming in the strWhere but after fixing that, its still not doing anything. I'm thinking the strWhere is probably not working.. maybe..

 

[TJPoorman]

what do you mean you tried debug without producing anything? it should have printed at least the first bit of text in the immediate window

 

[pr2-eugin]

No, I definitely gave that a shot. Tried again just to be sure. No message box pop's up. Its not generating anything..

This denotes the code is not triggered at all.. As the MsgBox should pop up, I have wrapped it in Nz function which in case if it returns Null will throw a Zero else a Number will pop up.. Try debugging/stepping through your code..

 

[gemma-the-husky]

i think a key issue is here is going to be your order number

if an order number is a number, than a leading zero shouldn't matter

if it's text, and an order number is "01234", then the order number is "01234" and not "1234". "1234" will be a different order.

I think part of the soultion might be to control the input better, eg find a way of preventing the order being input without the leading zero.

It must be better to do this, than to drift into a "fuzzy" solution for something as critical as an order number.

 

[Cirrostratus]

Thanks for all the suggestions!!

So, I was able to solve it. This was some really creepy stuff and I really just stumbled over this.

The Lot_Size_AfterUpdate was not being triggered at all. Nothing in there was working, whatever I put in it just would not work. So I went back to the form and wondered if there is a setup somewhere in the properties window that could be preventing it from working. I looked at the Event tab and noticed that it was completely blank. The afterupdate had nothing in it even though the code was there in VB. So I clicked that, selected Code Builder from the pop-up and it took me right to the VB afterupdate code. I clicked save and the damn thing started working. All the debugs and msgboxes started popping up when I entered an order. For some reason Access had lost the reference from the form to the VB. Its some really creepy stuff, I haven't seen that ever before!

Super Thanks for everyone on the help and suggestions! I hope someday soon I can help someone else troubleshoot their project! Thanks again!!

 

[pr2-eugin]

The Lot_Size_AfterUpdate was not being triggered at all.

Thought that might be the case.. Glad you have it sorted..

I hope someday soon I can help someone else troubleshoot their project! Thanks again!!

Sure you will.. Everyone here started somewhere.. Glad we could be of help.. Good luck..