Dlookup (criteria conflict)

[cynapattery]

Hi All,

I have a problem with Dlookup.

I have s search function which should select a name corresponding to ibin number and then from the bound table, populate the text boxes in the form.

this is the code I used. any body please tell me why thsi Dlookup is not giving the required output??

Var_Haendler2 = DLookup("[Haendlername]", "Tbl_map_Haendler", "[IBIN]=" & Forms!frm_Trigger_Storno.txt_IBIN_Srch)
DoCmd.GoToControl "Haendlername"
DoCmd.FindRecord Var_Haendler2

Var_haendler2 should have the corresponding handlername who has the ibin number entered in the text field. in the same form tehre is a bound text box Haendlername where the control will be later on and it should populate the form.

DoCmd.GoToControl "Haendlername"
DoCmd.FindRecord Var_Haendler2

I already use in a search function and it works perfect. I do not understand why it does not work here..

please help

 

[plog]

What field type is [IBIN]? If its text you need to escape the value from your form like this;

"[IBIN]='" & Forms!frm_Trigger_Storno.txt_IBIN_Srch & "'"

 

[cynapattery]

Hi Plog, the IBIN has a field type text. and I changed it as you suggested, but still i have error message,
3075_syntax failure.... 'IBIN='12341'
anyidea where will be the problem??
thanks,

 

[pr2-eugin]

So is this how your DLookUp looks like?

Var_Haendler2 = DLookup("[Haendlername]", "Tbl_map_Haendler", "[IBIN]='" & Forms!frm_Trigger_Storno.txt_IBIN_Srch & "'")

Just wondering, is your txt_IBIN_Srch on the same form that the table is bound to? If so uou might as well try..

Var_Haendler2 = DLookup("[Haendlername]", "Tbl_map_Haendler", "[IBIN]=" & Me.txt_IBIN_Srch & "'")

 

[cynapattery]

It is not bound to a table. should it be?

 

[pr2-eugin]

Normally it should be bound, this is how Access is designed to work. Without bound forms, there would be excessive coding, complications. Make the Form bound and the Search control Unbound, then use the Search for record code..

 

[RainLover]

Further to Paul if he is on the right track.

The fields (Many) you display from the combo are from the Row Source and the bound column (One) in the control source.

 

[cynapattery]

Hi yea that is true. the textboxes to be populated according to the search result are bound text boxes. only the search function is an unbound text box.
and now, I tried the following way,

Var_Haendler2 = DLookup("[Haendlername]", "Tbl_map_Haendler", "IBIN='" & Me.txt_IBIN_Srch & "'")
MsgBox Var_Haendler2
DoCmd.OpenForm "frm_Trigger_Storno"
DoCmd.GoToControl Haendlername
DoCmd.FindRecord Var_Haendler2

and it works with Dlookup and get the value in a variable.
I get the msgbox with required data.
But now, it dowsnot fetch the data from database and populate all the bound fields.
any suggestions?? thanks for the help regarding Dlookup..

 

[pr2-eugin]

Okay forget about the DLookUp.. Try this code..

[COLOR=Blue][B]Private Sub searchButton_Click()[/B][/COLOR]
    Dim rs As DAO.Recordset
    If DCount("*", "Tbl_map_Haendler", "IBIN='" & Me.txt_IBIN_Srch & "'") <> 0 Then
       [COLOR=Green] ' Find the record that matches the control.[/COLOR]
        Set rs = Me.Recordset.Clone
        rs.FindFirst "IBIN='" & Me.txt_IBIN_Srch & "'"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    Else
        Call MsgBox("No such record exists in the Table.", vbCritical, "No match found")
    End If
    Set rs = Nothing
[COLOR=Blue][B]End Sub[/B][/COLOR]

I have assumed that you are using a button to Search.. Change the Sub name as per requirement.

 

[RainLover]

Guys

I must appologise.

For some reason I have been thinking Combo Box not DLookup. I must be getting confused with a different thread.

Please ignore my previous post. Sorry about that.

 

[cynapattery]

I made some small changes for the corresponding form and it words..
I wanted to search in a differet db. thats the only change I needed to make and it works.
but just out of curiosity, why does nt it work with docmd.gotocontrol?
I would like to know, so that I wont repeat that mistake again..


Thanks a load...!!

Private Sub Cmd_IBIN_search_Click()
Dim Var_Haendler2 As String

    
    If DCount("*", "Tbl_map_Haendler", "IBIN='" & Me.txt_IBIN_Srch & "'") <> 0 Then
    Var_Haendler2 = DLookup("[Haendlername]", "Tbl_map_Haendler", "IBIN='" & Me.txt_IBIN_Srch & "'")
        ' Find the record that matches the control.
        Dim rs As DAO.Recordset
        Set rs = Me.Recordset.Clone
        rs.FindFirst "Haendlername='" & Var_Haendler2 & "'"
        
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    Else
        Call MsgBox("No such record exists in the Table.", vbCritical, "No match found")
    End If
    Set rs = Nothing
End Sub

 

[pr2-eugin]

DoCmd.GoToControl just switches or in other words, sets the Focus on the Control.. What you should have had is DoCmd.FindRecord.

I feel DAO recordset is much cleaner.

Please Note, I have eliminated the need of DLookUp. You do not need it. Unless IBIN is not part of the RecordSource of the Form. Any Domain function is a simplified Query, so it might add a overload to the code if used a lot.

 

[cynapattery]

yea IBIN is not the part of the bound table in that form. it belogs to another table which maps only the ibin with name. the details about the name is given in bound table but not the ibin. thats why I used it again.
do u have any suggestions for that?? (to avoid dlookup.)

 

[pr2-eugin]

If that's the case, just simplify it as,

Private Sub Cmd_IBIN_search_Click()
    Dim rs As DAO.Recordset, Var_Haendler2 As String
    Var_Haendler2 = Nz(DLookUp("Haendlername", "Tbl_map_Haendler", "IBIN='" & Me.txt_IBIN_Srch & "'"), "N/A")
    If  Var_Haendler2 <> "N/A" Then
       [COLOR=Green] ' Find the record that matches the control.[/COLOR]
        Set rs = Me.Recordset.Clone
        rs.FindFirst "Haendlername='" & Var_Haendler2 & "'"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    Else
        Call MsgBox("No such record exists in the Table.", vbCritical, "No match found")
    End If
    Set rs = Nothing
End Sub

 

[cynapattery]

Thanks for such an optimised solution...!

If that's the case, just simplify it as,

Private Sub Cmd_IBIN_search_Click()
    Dim rs As DAO.Recordset, Var_Haendler2 As String
    Var_Haendler2 = Nz(DLookUp("Haendlername", "Tbl_map_Haendler", "IBIN='" & Me.txt_IBIN_Srch & "'"), "N/A")
    If  Var_Haendler2 <> "N/A" Then
       [COLOR=green]' Find the record that matches the control.[/COLOR]
        Set rs = Me.Recordset.Clone
        rs.FindFirst "Haendlername='" & Var_Haendler2 & "'"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    Else
        Call MsgBox("No such record exists in the Table.", vbCritical, "No match found")
    End If
    Set rs = Nothing
End Sub

 

[pr2-eugin]

Glad to be of help.. Good Luck..

 

[rickyfong]

Dim TTTTT As String

TTTTT = DLookup("Tcode", "Transaction", "Tcode = 'W12345'")
' Find the record that matches the control.
Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "Tcode='" & TTTTT & "'"

If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
Call MsgBox("No such record exists in the Table.", vbCritical, "No match found")
End If

I have changed just some variable names, but it couldn't locate the record for me even though it existed in the table. Any idea?? Am I missing something?? Thanks a lot!!

 

[RainLover]

There are some Wizards that will set this type of thing up for you automatically. Give them a go as it may make it easier to learn.

A bit of advice.

TTTTT and 12345 are bad names. They are meaningless.

Think about coming back in 2 Years or 1 Year or even 6 Months.

Do you think that you will remember what these names are about. Try using names (Every where) that have some meaning to the contex of the situation.