advanced search form command button

[yammoo]

hello to one and all,

I am trying to transfer certain information (name, address_id)from one form to another. I have linked a command button to the other form via the wizard and it worked great (option: open the form and find specific data to display).

I've worked in the dlookup function to handle part of my issue.

Ok, when I click the button and a pre-existing Invoice form is located it is fine and dandy.

When the button is clicked on a record and no pre-existing info is found the new form comes up blank. What I want is for it to come up partially filled with the client's name.

On the new form I have a combo box which should be able to take in the client's last name or client's company but it won't pass it properly. I am however able to pass a string to another combo on the same form.

If anyone has suggestions / questions / comments please let me know asap.

Thanks in advance,
yammoo

P.S. I have no problem pasting the code if you need it.

 

[boblarson]

P.S. I have no problem pasting the code if you need it.

That might help.

 

[yammoo]

code request fulfilled

Thanks in advance. Oh and thank you too Mr. Larson for taking the time to read my post!

ok my code is

Private Sub cmdOpenCorrespondingWO_Click()
On Error GoTo Err_cmdOpenCorrespondingWO_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Work Orders Table 10-25-06"
    
    stLinkCriteria = "[C_AddressID]=" & Me![txtClientAddressID]
    If DLookup("[C_AddressID]", "[Work Orders Table 10-25-06]", [stLinkCriteria]) Then
           DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
           DoCmd.OpenForm stDocName, , , , acFormAdd
           MsgBox "New Invoice needs to be created using"
           If Me.C_L_Name.Value <> "" Then
        [COLOR="RoyalBlue"]           Forms![Work Orders Table 10-25-06].Form![cmbChoice].Value = "Name"[/COLOR]
        [COLOR="Red"]           'Forms![Work Orders Table 10-25-06].Form![cmbClient1].Value = Me.C_L_Name.Value[/COLOR]
                   MsgBox "Last Name " & Me.C_L_Name.Value
           Else
                   Forms![Work Orders Table 10-25-06].Form![cmbChoice].Value = "Company"
                   'Forms![Work Orders Table 10-25-06].Form![cmbClient1].Value = Me.C_Associated_Co.Value
                   MsgBox "Company Name " & Me.C_Associated_Co.Value
           End If
    End If 
Exit_cmdOpenCorrespondingWO_Click:
    Exit Sub

Err_cmdOpenCorrespondingWO_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenCorrespondingWO_Click
    
End Sub

The blue part is the combo box that works and the red part is where my problem lies.

Please note that my code does work w/o error in access, but I want to take it up a level.

Oh and yes I do know that red part of my code is commented out. I commented it out because I knew it wasn't passing the variable that it was supposed to (It must be hiding it somewhere). And as for the second part of my code is pretty much identical and presents the same exact problem. So once I fix this portion, I will be able to duplicate it without any problems. I hope!

I think that a global variable is ridiculous only because a string passed to another combo box on that same form works perfectly well w/o issues.

Please let me know if you need to see screenshots of my two forms as well.

 

[boblarson]

Quick question:

cmbClient1 is the bound column an ID number or the Description Text?

Me.C_L_Name would need to be the same type. What I was thinking is that you might have two columns in cmbClient1 with the ID being the bound column and hidden and the description shows, while Me.C_L_Name has only one column and it is the description (text). So, you couldn't match them up.

 

[yammoo]

answer to question

mr. larson,

yes, you are right in assuming that my bound column is an id number and that my combo box has two columns attached to it. also now that i think of it, that particular combo box (cmbClient1) is bound to a query referenced in the on focus event.

so my thoughts are as follows,

Forms![Work Orders Table 10-25-06].Form![cmbClient1].Column(0) = Me.[Client No].value
Forms![Work Orders Table 10-25-06].Form![cmbClient1].Column(1) = Me.C_L_Name.Value

i have a feeling, I'm in the right direction, but not 100 percent sure.

 

[boblarson]

You aren't going to be able to set two values for cmbClient1. Either it will be Client No. or it will be the C_L_Name, but you can't set it to both unless the Client No is the same as the C_L_Name, in which case you don't have to set the name anyway because if you set the bound column to be what it is supposed to be, the displayed text will be what you want.

 

[yammoo]

only tried one

what you say makes sense, since I would only need one or the other anyways if they are one in the same. I will stick with the client number because within the query, the first name and last name fields are concatenated.

I have tried working with the code I posted earlier and it keeps giving me the error "Property Let Procedure not defined and property get procedure did not return an object". despite my double checking and triple checking my bound column, the error doesn't change.

strange when i google the error, it brings up solutions concerning excel......weird

 

[boblarson]

Not sure about the Propery Let Procedure error. If I were you I would go to Debug > Compile and see if that goes to any errors.

Also, you don't need to put .Value after the controls. .Value is the default for Combo boxes, Text Boxes, List Boxes, Check Boxes and more.

 

[yammoo]

continue working on own....

i think i shall ponder this on my own for a little while and if i come up with the solution, I will definitely let you know mr. larson.

i am gonna give the problem a rest for now, however if anyone has any possible solutions, comments, or questions regarding this because they were unclear. do not hesitate to post a reply.

thanks again mr. larson. <tips hat> </tip>

 

[yammoo]

possible event code misguiding results

i think i came across a possible drawback to having my cmbClient1 box do other things such as load an existing query when the user enters into it or when the user changes the value it loads another combo box with that client's corresponding address.

my code is

Private Sub cmbClient1_enter()
    Me.cmbClient1.RowSource = "cmbClient1query"
End Sub

and it may be the thing preventing me from loading a name from the other client form.

perhaps i can use an if statement such as 
Private Sub cmbClient1_enter()
if on_load = basic client info form ' other form that info is loaded from
then
grab name from basic client info
else
    Me.cmbClient1.RowSource = "cmbClient1query"
end if

does anyone have any ideas on how to code this. i have somewhat of an idea, but any help would be appreciated.

thanks in advance,
yammoo

 

[yammoo]

i have managed to get it to work. for those that are interested in the solution is that i had to remove the .column in red portion of the code in my post above. by simply removing that my code worked like a charm. i had to make a few minor additons such as requery my combo boxes and the like. thanks to mr. larson for all your help on this issue.