Enter Parameter Value

[Gazza2]

ok im am not sure if this is in the right place but it seems to be a form problem.
i have a form that has a textbox(text1) which is used to enter a supplier name, the user then presses enter and another form opens up with all the supplier details.ok.
the code i have to do this is on the afterupdate of text1 which is unbound is
DoCmd.openform"SupplierDetails",,,"[Supplier Name]=" & Supplier name
when i use this code and the enter key is pressed an `Enter Parameter Value` dialog box appears with the supplier name under the blue title bar.if i then enter the supplier name again it opens the second form and displays all the details i want.
If i use the code
DoCmd.openform"SupplierDetails",,,"[Supplier Name]"= me.Supplier_name
it opens up the second form without the dialog box but it doesnt display any details.
is ther any way to adjust the first code so the dialog box isnt displayed and just goes straight to the second form and displaying the details.
i have gone through as many posts as i can but cant seem to find a fix.

any help would be appriciated thanks

 

[wazz]

what if u change your original:
DoCmd.openform"SupplierDetails",,,"[Supplier Name]=" & Supplier name
to
DoCmd.openform"SupplierDetails",,,"[Supplier Name]=" & Forms!YourFormName!Supplier name

 

[Gazza2]

runtime error

i now getrun-time error 2450
microsoft access can`t findthe form `supplierDetails` referred to in a macro expression or visual basic code

 

[RuralGuy]

Almost certainly [Supplier Name] is a string! (Bad idea to have spaces in names by the way)

Try:

DoCmd.openform"SupplierDetails",,,"[Supplier Name] = """ & me.Supplier_name & """

OR

DoCmd.openform"SupplierDetails",,,"[Supplier Name] = '" & me.Supplier_name & "'"

 

[Gazza2]

Excellent thanks very much for your help the first one worked a treat.

 

[RuralGuy]

You're welcome and thanks for posting back with your success.

 

[Gazza2]

Can you use this code to copy 2 parts of information over, if so could you please tell me the changes i need to make, Or is it completely different code.

The new information is another text box named Ref


Thanks in advance

 

[RuralGuy]

Copied right from the "Help" system:

expression.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

Use the "OpenArgs" to pass any number of pieces of information. Right now you are just using the "WhereCondition" parameter. It can all be done with the one "OpenForm" call.

 

[Gazza2]

Sorry but what is the "OpenArgs" i have not come across that yet

 

[RuralGuy]

I will be glad to assist but have you looked up OpenArgs in the Visual Basic help yet?

 

[Gazza2]

Do you have to have a macro set up to use the openargs

i think this has just fried my brain

 

[RuralGuy]

No macro required, just a little VBA.

Dim strWhere As String
Dim strForm As String
Dim strArguments As String

strForm = "SupplierDetails"
strWhere = "[Supplier Name] = """ & me.Supplier_name & """
strArguments = "String1" & ";"
strArguments = strArguments & "String2" & ";"
strArguments = strArguments & "String3" 

[B]'** strArguments is now = to: "String1;String2;String3"[/B]

DoCmd.openform strForm, , , strWhere, , , strArguments

In the open event of the receiving form put:

Private Sub Form_Open(Cancel As Integer)

Dim Args as Variant

If Not IsNull(Me.OpenArgs) Then
    '-- Form is being opened from a form passing parameters
    Args = Split(Me.OpenArgs, ";") ' Split into 0 based array
    Me.txtBox1 = Args(0)
    Me.txtBox2 = Args(1)
    Me.txtBox3 = Args(2)
End If

End Sub

That is really all there is to it and it keeps things neat and tidy and modular!

 

[Gazza2]

Sorry for being aa pain but i entered the code and i now get a runtime error telling me i cant assign a value to the supplier name.

 

[RuralGuy]

It is always a little rough at first until you get some of the code running. There must be a typo somewhere because we're not trying to assign a value to the supplier name. Did you implement the OpenArgs method of passing data? If so how about posting the same two sections of code that I supplied to you. We'll make things work!

 

[Gazza2]

here goes

ok the two pieces of information ar the supplier name and a ref which i want to pas onto the other form. the code i have in the afterupdate of the ref textbox is :-
Private Sub Ref_AfterUpdate()
Dim strWhere As String
Dim strform As String
Dim strarguments As String

strform = "Supplier Details"
strWhere = "[ref]=""" & Me.Ref & """"
strarguments = "string1" & ";"
strarguments = strarguments & "string2" & ";"
strarguments = strarguments & "string3"
'** strarguments is now = to: "string1;string2;string3"

DoCmd.OpenForm strform, , , strWhere, , , strarguments

End Sub

And in the on open event of the supplier details form i have:-
Private Sub Form_Open(Cancel As Integer)

Dim args As Variant

If Not IsNull(Me.OpenArgs) Then
'-- form is being opened from a form passing parameters
args = Split(Me.OpenArgs, ";") ' Split into 0 based array
Me.Supplier = args(0)
Me.Ref = args(1)

End If

End Sub

 

[RuralGuy]

There is an extra " at the end of the first line:

strWhere = "[ref]=""" & Me.Ref & """" [B]<--- should only be 3 " here[/B]
strarguments = "string1" & ";"
strarguments = strarguments & "string2" & ";"
strarguments = strarguments & "string3"
'** strarguments is now = to: "string1;string2;string3"

and you need to set the strArguments with something like:

strArguments = Me.Supplier & ";"
strArguments = strArguments & Me.Ref

As a diagnostic, lets put the following in the Form_Open event

If Not IsNull(Me.OpenArgs) Then
'-- form is being opened from a form passing parameters
[B]MsgBox "Incoming Arguments are [" & Me.OpenArgs & "]"[/B]
args = Split(Me.OpenArgs, ";") ' Split into 0 based array
Me.Supplier = args(0)
Me.Ref = args(1)
End If

 

[Gazza2]

so where you have typed string1, string2 etc i need to type me.(info to pass to next form)

ok
the diagnostic msgbox say the args are [me.supplier name;me.ref;]

but even with the changes i still get the runtime error

 

[RuralGuy]

Your 2 lines should look something like this:

strArguments = Me.[supplier name] & ";"
strArguments = strArguments & Me.Ref

I'll bet they look more like:

strArguments = "Me.[supplier name] " & ";"
strArguments = strArguments & "Me.Ref" & ";"

Computers are really dumb in that they do what they are told and not necessarily what you want them to do.

Do you have controls on the first form named [supplier name] and Ref?
If not this will not work. Because you have a space in "supplier name" you must enclose the name in brackets []. And *no* semicolon ";" after the last argument. Try it one more time and we'll see what gets to the next form.

 

[Gazza2]

ok sorry to take up all your time.

the first part is there. the message box now tells me what i have typed into the textboxes but it still wont open the second form with the data.

when you run the debug the yellow line is over the me.supplier_name = args(0) line

i have checked all the textbox names and the names are correct

 

[Gazza2]

sorted

i found that the problem was that the second form was designed from a table.

i re-did the form and it now warks perfectly.

thanks very much for all your help.