Working Dlookup code not working

[Gazza2]

Hope someone can help with this very strange but seriously annoying problem.

I have a form with an unbound text box(TxtPONo) that has a number entered into it and if it matches a number in my table(TblTransactions) then it should open a form(FrmGR). Seems simple enough and here is the code i am using:

If Me.TxtPONo = DLookup("DocumentNo", "TblTransactions", "DocumentNo = " & Me.TxtPONo) Then
DoCmd.OpenForm "FrmGR"
Else
MsgBox "Not working", vbOKOnly
End If

The code in red is just there for testing purposes. This code is in the afterupdate event of the text box.

What makes this problem even stranger is i have similar code just with different table and that is working perfectly. I just cant seem to figure out why it wont open the form when i enter a number that i know is in the table.

Any help would be much appreciated.

Thanks
Gareth

 

[boblarson]

Dlookups can be a bit touchy. If you are just concerned with checking to see if the value exists, just use DCount instead:

If DCount("*", "TblTransactions", "DocumentNo = " & Me.TxtPONo)> 0 Then

If DocumentNo is text then you would need:

If DCount("*", "TblTransactions", "DocumentNo = " & Chr(34) & Me.TxtPONo & Chr(34))> 0 Then

 

[Gazza2]

Thanks bob that works a treat.

Its still bugging me that i can get it to work on one form but not another, I also have the second part of the code(finding a record) in the on open event of the form(FrmGR) and that doesnt work either.

I,m thinking maybe there is a bit of corruption going on or am i just being a bit thick.

Thanks
Gareth

 

[boblarson]

It's possible that you have some corruption going on. Try making a copy of the database (as a safeguard) and then do a DECOMPILE and then COMPILE and then run compact and repair and see if that helps. If not, try importing everything into a new, blank database shell.

 

[Gazza2]

Ok bob i have tried your ideas and am still having trouble making dlookup work so i assume its the way the database is set out or something so have you any ideas what could be causing this

Thanks
Gareth

 

[boblarson]

Ok bob i have tried your ideas and am still having trouble making dlookup work so i assume its the way the database is set out or something so have you any ideas what could be causing this

Thanks
Gareth

Can you post a copy of your database (with bogus data of course) so we can take a look and see if we can spot what's going on? If you do post it, be sure to specify which form, which control, which query, etc. is the problem we should look at.

 

[Gazza2]

Ok bob ive uploaded the db as requested.

The problem is with the FrmGRSelect, if you enter s into the first textbox and select a supplier from the list this filters the listbox.
Then if you go to the textbox at the bottom of the form and enter a number for a record it should in theory open up frmgoodsrecieve and display the selected record. I have this work in another form on another db but just cant seem to get it working here.

As a side note the second text box on FrmGRSelect also has a dlookup that isnt working but if i can find out why the first dlookup isnt working then i can probably get that working.

Thanks for your time and patience

Gareth

 

[boblarson]

Okay, you have several things going on which are either not necessary or not a good way of doing it.

I don't know why you were even using the DLookup except the best I could make out was you wanted to make sure the value was in the table. So, if that was the case this is a better way:

Private Sub TxtSelect1_AfterUpdate()
If DCount("*", "TblOrders", "DocumentNo = " & Me.TxtSelect1) > 0 Then
    DoCmd.OpenForm "FrmGoodsRecieve", acNormal
Else
   MsgBox "Incorrect selection", vbOKOnly
   Me.TxtSelect1 = Null
End If
End Sub

And I am confused with what you are trying to do here:

Private Sub Form_Open(Cancel As Integer)
'Me.DocumentNo = Forms!FrmGRSelect.TxtPONo.Value
Dim account As String
If Forms!FrmGRSelect!List4.Visible = True Then
    account = Form_FrmGRSelect.List4.Column(1, Form_FrmGRSelect.TxtSelect1)
Else
    MsgBox "Not found", vbOKOnly
    'account = Form_FrmGRSelect.TxtPONo.Value
End If
DoCmd.FindRecord account, acEntire, True, acSearchAll, , acCurrent, True
Me.FrmGRSub.SetFocus
End Sub

What are you trying to find?

 

[Gazza2]

ok i tried the code in the first box, this still gives me the message box "incorrect selection" if i enter one of the numbers in the line column.

The second part of code is supposed to find the related record for the documentno of the record that you selected in the first form.

i.e
type supp2 into the supplier text box and press enter
press enter again
this gives you three lines
if you type 1 into the textbox at the bottom of the form this should open up frmgoodsrecieve with the related record for documentno 13.

Thanks
Gareth

P.S I have changed the first line of the second part of code from "dim account as string" to "dim account as long"

 

[boblarson]

ok i tried the code in the first box, this still gives me the message box "incorrect selection" if i enter one of the numbers in the line column.

Yes, it has to be in the DOCUMENT column. The line numbers don't exist in the table. They are a function of the query.

The second part of code is supposed to find the related record for the documentno of the record that you selected in the first form.

There is a much better way. Open the form filtered.

Private Sub TxtSelect1_AfterUpdate()
If DCount("*", "TblOrders", "DocumentNo = " & Me.TxtSelect1) > 0 Then
    DoCmd.OpenForm "FrmGoodsRecieve", acNormal, , "DocumentNo = " & Me.TxtSelect1
Else
   MsgBox "Incorrect selection", vbOKOnly
   Me.TxtSelect1 = Null
End If
End Sub

 

[boblarson]

Uploading the working copy

 

[Gazza2]

Thanks bob that works.

But(sorry i just get annoyed when i cant make something work that i have had working before.)

The problem is that eventually the DocumentNo will be 5 numbers long and the top 15 in the list will probably be the only ones that are selected so this is why i have the line number in the list box and why i would like to be able to select by the line number rather than the DocumentNo.

If you dont mind me being a pain in the rear, could you please just have a look at another db that i have uploaded that has the code working as i would like it to.

If you select FrmCustomerSelect and enter cus into the text box it lists the two customers matching this.
If you then enter either 1 or 2(the line number (or sequence number in the code)) then it opens up FrmCustomerEnquiry with the selected customer.

Thanks (and sorry for being a pain)
Gareth

 

[boblarson]

Thanks bob that works.

But(sorry i just get annoyed when i cant make something work that i have had working before.)

The problem is that eventually the DocumentNo will be 5 numbers long and the top 15 in the list will probably be the only ones that are selected so this is why i have the line number in the list box and why i would like to be able to select by the line number rather than the DocumentNo.

If you dont mind me being a pain in the rear, could you please just have a look at another db that i have uploaded that has the code working as i would like it to.

I don't think you've tested enough. The code has nothing to do with line number. You are trying to match account code in the select text box. I think you are quite a ways off from having it really working as you want.

Also, when in code you don't refer to forms this way:

Form_FrmCustomerSelect.

That usage can introduce strange behavior. You use the forms collection to refer to them:

Forms!FrmCustomerSelect.

or

Forms("FrmCustomerSelect).

If you select FrmCustomerSelect and enter cus into the text box it lists the two customers matching this.
If you then enter either 1 or 2(the line number (or sequence number in the code)) then it opens up FrmCustomerEnquiry with the selected customer.

It is only because the account code matches the line number. It is a coincidence.

Thanks (and sorry for being a pain)

No problem, but I am concerned as I see a lot of unecessary code in there. I think we need to work through those issues. I'm really busy for a while so I don't know how much help I can provide. But we should go over several things and get it more efficient and compact (as well as working correctly).

 

[Gazza2]

Thanks for the help so far Bob.

I am uploading a revised version of the first db that sort of does what i want but it doesnt find the whole record.

I can get the second form to open up with only the DocumentNo text bo filled in but cant figure out how to get the whole record.

When you get time could you just have a look and point me in the right direction( There is no rush whatsoever so only when you get time)

No problem, but I am concerned as I see a lot of unecessary code in there. I think we need to work through those issues. I'm really busy for a while so I don't know how much help I can provide. But we should go over several things and get it more efficient and compact (as well as working correctly).

This db is only for testing so i still have to go through it and delete any unecessary code when i figure out what is useless and what is not but i was just trying to get it doing what i wanted first(which isnt as easy as i first thought)

Many Thanks
Gareth

 

[boblarson]

Thanks for the help so far Bob.

I am uploading a revised version of the first db that sort of does what i want but it doesnt find the whole record.

I can get the second form to open up with only the DocumentNo text bo filled in but cant figure out how to get the whole record.

When you get time could you just have a look and point me in the right direction( There is no rush whatsoever so only when you get time)



This db is only for testing so i still have to go through it and delete any unecessary code when i figure out what is useless and what is not but i was just trying to get it doing what i wanted first(which isnt as easy as i first thought)

Many Thanks
Gareth

Okay, Sorry to say but you have some serious issues with your database. You are completely messed up with what you are doing. I don't mean to sound like I'm getting down on you but I do want to point out only a few issues which are contributing to your problems.

Do NOT use this syntax for referring to forms:

Form_frmGoodsReceive

You need to use

Forms!frmGoodsReceive

Using the Form_ method has various complications which arise and can cause you suffering.

Next, Your code for opening the other form should be like this:

Private Sub TxtSelect1_AfterUpdate()
Dim account As Integer

If Me.TxtSelect1 = [B]Me.[/B]List4.Column(0, TxtSelect1.Value) Then
   DoCmd.OpenForm "FrmGoodsRecieve", acNormal, , [B]"[DocumentNo]=" & Me.List4.Column(0, Me.TxtSelect1)[/B], acFormEdit, acWindowNormal
Else
    MsgBox "Incorrect Option", vbOKOnly
End If
'DoCmd.Close acForm, "FrmMasterMenu"
End Sub

Do NOT put "" at the end for the OpenArgs. If you don't have an argument to fill in, you just leave it blank and if it is the last one you leave off the last comma as well.

Next -
In your form frmGoodsReceive you have a subform and that subform has 3 tables in the underlying query. It can't have that. You have a One-To-Many-To-Many relationship with those and therefore you really need sub-subform(s).

Next -
Your tables are incorrectly set up. You have SupplierID in each of the four tables. It should ONLY be in the Suppliers table (as Primary Key) and in the components table (as Foreign Key). You should NOT have it in the Transactions or Orders table.

There may be more but that's what I have found with the time I could spare.

 

[Gazza2]

Bob you are a superstar.
you are the expert and i am the newbie asking questions that you have probably been asked a million times so you are not getting down on me.

I finally got it working with the following code

Private Sub TxtSelect1_AfterUpdate()
Dim account As Integer

If Me.TxtSelect1 = List4.Column(0, TxtSelect1.Value) Then
   account = Forms!FrmGRSelect.List4.Column(2, Forms!FrmGRSelect.TxtSelect1)
DoCmd.OpenForm "FrmGoodsRecieve", acNormal, , "[DocumentNo]=" & account, acFormEdit, acWindowNormal
Else
    MsgBox "Incorrect Option", vbOKOnly
End If

End Sub

I have also fixed the other issues you have picked up on except the placement of supplierID in the components table(couldnt get it to work) so i have taken it out of the components table and added it to the orders table.

That will be another question for another day though.

Until then Many thanks for all you help

Gareth

 

[boblarson]

Bob you are a superstar.
you are the expert and i am the newbie asking questions that you have probably been asked a million times so you are not getting down on me.

I finally got it working with the following code

Private Sub TxtSelect1_AfterUpdate()
Dim account As Integer

If Me.TxtSelect1 = List4.Column(0, TxtSelect1.Value) Then
   account = Forms!FrmGRSelect.List4.Column(2, Forms!FrmGRSelect.TxtSelect1)
DoCmd.OpenForm "FrmGoodsRecieve", acNormal, , "[DocumentNo]=" & account, acFormEdit, acWindowNormal
Else
    MsgBox "Incorrect Option", vbOKOnly
End If
 
End Sub

This line isn't needed and that's why I had put what I put in your code:
account = Forms!FrmGRSelect.List4.Column(2, Forms!FrmGRSelect.TxtSelect1)

No need to use a variable for a single line. If you were going to use account more than once in the procedure that is another matter, but you just needed it for the Filter and the form reference wasn't all that long.