two form based on the same parameter query

T

TC

I have two separate forms that are based on the same parameter query, frmSOR
and frmQuote. I am using the following criteria for the query
WHERE (tblCustomer.[Company Name]=forms!frmQuote!CompanyName
On both of the forms CompanyName is an unbound combobox.

I can not figure out how to open just the form frmSOR and not get the "Set
Parameter" box open without having to have frmQuote open.

I have looked through many post and I think the easiest solution is to some
how set the CompanyName field as a Public string in a module. But I am not
sure how to do this or if this is even the correct solution.

Any help would be much appreciated.
Thanks
TC
 
D

Dale Fye

TC,

Since you cannot use global variables in a query, that really won't work.
The way I handle this is to create a function in a code module (not a forms
class module). The function would look something like:

Public Function fnCoName(Optional CoName as Variant = NULL) as String

Static myCoName as string

if NOT IsNull(CoName) then myCoName = CoName
fnCoName = myCoName

End function

The way this function works is that if you pass it a value, it sets the
myCoName variable in the function, and returns that value to the calling
code. Since this (myCoName) is a static variable, it retains its value
between function calls. If you don't pass the function a value, it returns
the value that is already stored in myCoName.

Now, in the CompanyName combo boxes AfterUpdate event, enter the following
code:

Private Sub cbo_CompanyName_AfterUpdate

Call fnCoName(me.cbo_CompanyName)
'or you could use
'fnCoName me.cbo_CompanyName

End Sub

Then, in your query, change the where clause to:

WHERE tblCustomer.[Company Name] = fnCoName()

Now, you can use this value anywhere in your application.

HTH
Dale
 
T

TC

Dale
I am getting a Compile Error: "Wrong number of arguments" for the after
update event.
Any ideas?

Private Sub cboCompanyName_AfterUpdate()
fnCoName (Me.cboCompanyName)
End Sub

Dale Fye said:
TC,

Since you cannot use global variables in a query, that really won't work.
The way I handle this is to create a function in a code module (not a forms
class module). The function would look something like:

Public Function fnCoName(Optional CoName as Variant = NULL) as String

Static myCoName as string

if NOT IsNull(CoName) then myCoName = CoName
fnCoName = myCoName

End function

The way this function works is that if you pass it a value, it sets the
myCoName variable in the function, and returns that value to the calling
code. Since this (myCoName) is a static variable, it retains its value
between function calls. If you don't pass the function a value, it returns
the value that is already stored in myCoName.

Now, in the CompanyName combo boxes AfterUpdate event, enter the following
code:

Private Sub cbo_CompanyName_AfterUpdate

Call fnCoName(me.cbo_CompanyName)
'or you could use
'fnCoName me.cbo_CompanyName

End Sub

Then, in your query, change the where clause to:

WHERE tblCustomer.[Company Name] = fnCoName()

Now, you can use this value anywhere in your application.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



TC said:
I have two separate forms that are based on the same parameter query, frmSOR
and frmQuote. I am using the following criteria for the query
WHERE (tblCustomer.[Company Name]=forms!frmQuote!CompanyName
On both of the forms CompanyName is an unbound combobox.

I can not figure out how to open just the form frmSOR and not get the "Set
Parameter" box open without having to have frmQuote open.

I have looked through many post and I think the easiest solution is to some
how set the CompanyName field as a Public string in a module. But I am not
sure how to do this or if this is even the correct solution.

Any help would be much appreciated.
Thanks
TC
Click to expand...
Click to expand...
 
T

TC

Dale,
Please ignore my previous post I had an error in the Module code. However I
can now select the company from the cbo but the query is not returning any
results.
AfterUpdate Code:
Private Sub cboCompanyName_AfterUpdate()
Call fnCoName(Me.cboCompanyName)
End Sub
Query:
SELECT tblCustomer.CustomerID, tblCustomer.[Company Name],
tblCustomer.First, tblCustomer.Last, tblCustomer.Street1,
tblCustomer.Street2, tblCustomer.City, tblCustomer.State, tblCustomer.Zip,
tblCustomer.Officephone, tblCustomer.Cellphone, tblCustomer.Email,
tblCustomer.WebPage, tblCustomer.Notes
FROM tblCustomer
WHERE tblCustomer.[CompanyName] = fnCoName();

Any Ideas?
Thank you for your help
TC

TC said:
Dale
I am getting a Compile Error: "Wrong number of arguments" for the after
update event.
Any ideas?

Private Sub cboCompanyName_AfterUpdate()
fnCoName (Me.cboCompanyName)
End Sub

Dale Fye said:
TC,

Since you cannot use global variables in a query, that really won't work.
The way I handle this is to create a function in a code module (not a forms
class module). The function would look something like:

Public Function fnCoName(Optional CoName as Variant = NULL) as String

Static myCoName as string

if NOT IsNull(CoName) then myCoName = CoName
fnCoName = myCoName

End function

The way this function works is that if you pass it a value, it sets the
myCoName variable in the function, and returns that value to the calling
code. Since this (myCoName) is a static variable, it retains its value
between function calls. If you don't pass the function a value, it returns
the value that is already stored in myCoName.

Now, in the CompanyName combo boxes AfterUpdate event, enter the following
code:

Private Sub cbo_CompanyName_AfterUpdate

Call fnCoName(me.cbo_CompanyName)
'or you could use
'fnCoName me.cbo_CompanyName

End Sub

Then, in your query, change the where clause to:

WHERE tblCustomer.[Company Name] = fnCoName()

Now, you can use this value anywhere in your application.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



TC said:
I have two separate forms that are based on the same parameter query, frmSOR
and frmQuote. I am using the following criteria for the query
WHERE (tblCustomer.[Company Name]=forms!frmQuote!CompanyName
On both of the forms CompanyName is an unbound combobox.

I can not figure out how to open just the form frmSOR and not get the "Set
Parameter" box open without having to have frmQuote open.

I have looked through many post and I think the easiest solution is to some
how set the CompanyName field as a Public string in a module. But I am not
sure how to do this or if this is even the correct solution.

Any help would be much appreciated.
Thanks
TC
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

TC,

How many columns do you have in your combo box? I usually have an ID field,
and a text field. If you have two, and the bound column is #1 (ID) then you
would need to change the AfterUpdate code to read:

Call fnCoName(me.cboCompanyName.column(1)

If that is not it, lets test to make sure the function is working properly.
Open the VB Editor and display the Immediate window.

1. Type: ?fnCoName("Test")
2. you should see: Test
3. Type: ?fnCoName
4. you should see: Test

Now open your application and select a company from one of the combo boxes
on either form.
Go back to the immediate window and type: ?fnCoName
You should see the value of the company name that was passed by your
AfterUpdate event.

HTH
Dale


TC said:
Dale,
Please ignore my previous post I had an error in the Module code. However
I
can now select the company from the cbo but the query is not returning any
results.
AfterUpdate Code:
Private Sub cboCompanyName_AfterUpdate()
Call fnCoName(Me.cboCompanyName)
End Sub
Query:
SELECT tblCustomer.CustomerID, tblCustomer.[Company Name],
tblCustomer.First, tblCustomer.Last, tblCustomer.Street1,
tblCustomer.Street2, tblCustomer.City, tblCustomer.State, tblCustomer.Zip,
tblCustomer.Officephone, tblCustomer.Cellphone, tblCustomer.Email,
tblCustomer.WebPage, tblCustomer.Notes
FROM tblCustomer
WHERE tblCustomer.[CompanyName] = fnCoName();

Any Ideas?
Thank you for your help
TC

TC said:
Dale
I am getting a Compile Error: "Wrong number of arguments" for the after
update event.
Any ideas?

Private Sub cboCompanyName_AfterUpdate()
fnCoName (Me.cboCompanyName)
End Sub

Dale Fye said:
TC,

Since you cannot use global variables in a query, that really won't
work.
The way I handle this is to create a function in a code module (not a
forms
class module). The function would look something like:

Public Function fnCoName(Optional CoName as Variant = NULL) as String

Static myCoName as string

if NOT IsNull(CoName) then myCoName = CoName
fnCoName = myCoName

End function

The way this function works is that if you pass it a value, it sets the
myCoName variable in the function, and returns that value to the
calling
code. Since this (myCoName) is a static variable, it retains its value
between function calls. If you don't pass the function a value, it
returns
the value that is already stored in myCoName.

Now, in the CompanyName combo boxes AfterUpdate event, enter the
following
code:

Private Sub cbo_CompanyName_AfterUpdate

Call fnCoName(me.cbo_CompanyName)
'or you could use
'fnCoName me.cbo_CompanyName

End Sub

Then, in your query, change the where clause to:

WHERE tblCustomer.[Company Name] = fnCoName()

Now, you can use this value anywhere in your application.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have two separate forms that are based on the same parameter query,
frmSOR
and frmQuote. I am using the following criteria for the query
WHERE (tblCustomer.[Company Name]=forms!frmQuote!CompanyName
On both of the forms CompanyName is an unbound combobox.

I can not figure out how to open just the form frmSOR and not get the
"Set
Parameter" box open without having to have frmQuote open.

I have looked through many post and I think the easiest solution is
to some
how set the CompanyName field as a Public string in a module. But I
am not
sure how to do this or if this is even the correct solution.

Any help would be much appreciated.
Thanks
TC
Click to expand...
Click to expand...
Click to expand...
 
T

TC

Dale
Thank you that helped me find the error. It was a simple typo.
One last question when I open one one of the forms that calls the parameter
query is it possible for the the query to return a null value similar to when
you open the form for the first time? This way all of the fields are blank
upon reopening the form?
Thanks
TC

Dale Fye said:
TC,

How many columns do you have in your combo box? I usually have an ID field,
and a text field. If you have two, and the bound column is #1 (ID) then you
would need to change the AfterUpdate code to read:

Call fnCoName(me.cboCompanyName.column(1)

If that is not it, lets test to make sure the function is working properly.
Open the VB Editor and display the Immediate window.

1. Type: ?fnCoName("Test")
2. you should see: Test
3. Type: ?fnCoName
4. you should see: Test

Now open your application and select a company from one of the combo boxes
on either form.
Go back to the immediate window and type: ?fnCoName
You should see the value of the company name that was passed by your
AfterUpdate event.

HTH
Dale


TC said:
Dale,
Please ignore my previous post I had an error in the Module code. However
I
can now select the company from the cbo but the query is not returning any
results.
AfterUpdate Code:
Private Sub cboCompanyName_AfterUpdate()
Call fnCoName(Me.cboCompanyName)
End Sub
Query:
SELECT tblCustomer.CustomerID, tblCustomer.[Company Name],
tblCustomer.First, tblCustomer.Last, tblCustomer.Street1,
tblCustomer.Street2, tblCustomer.City, tblCustomer.State, tblCustomer.Zip,
tblCustomer.Officephone, tblCustomer.Cellphone, tblCustomer.Email,
tblCustomer.WebPage, tblCustomer.Notes
FROM tblCustomer
WHERE tblCustomer.[CompanyName] = fnCoName();

Any Ideas?
Thank you for your help
TC

TC said:
Dale
I am getting a Compile Error: "Wrong number of arguments" for the after
update event.
Any ideas?

Private Sub cboCompanyName_AfterUpdate()
fnCoName (Me.cboCompanyName)
End Sub

:

TC,

Since you cannot use global variables in a query, that really won't
work.
The way I handle this is to create a function in a code module (not a
forms
class module). The function would look something like:

Public Function fnCoName(Optional CoName as Variant = NULL) as String

Static myCoName as string

if NOT IsNull(CoName) then myCoName = CoName
fnCoName = myCoName

End function

The way this function works is that if you pass it a value, it sets the
myCoName variable in the function, and returns that value to the
calling
code. Since this (myCoName) is a static variable, it retains its value
between function calls. If you don't pass the function a value, it
returns
the value that is already stored in myCoName.

Now, in the CompanyName combo boxes AfterUpdate event, enter the
following
code:

Private Sub cbo_CompanyName_AfterUpdate

Call fnCoName(me.cbo_CompanyName)
'or you could use
'fnCoName me.cbo_CompanyName

End Sub

Then, in your query, change the where clause to:

WHERE tblCustomer.[Company Name] = fnCoName()

Now, you can use this value anywhere in your application.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have two separate forms that are based on the same parameter query,
frmSOR
and frmQuote. I am using the following criteria for the query
WHERE (tblCustomer.[Company Name]=forms!frmQuote!CompanyName
On both of the forms CompanyName is an unbound combobox.

I can not figure out how to open just the form frmSOR and not get the
"Set
Parameter" box open without having to have frmQuote open.

I have looked through many post and I think the easiest solution is
to some
how set the CompanyName field as a Public string in a module. But I
am not
sure how to do this or if this is even the correct solution.

Any help would be much appreciated.
Thanks
TC
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

Sure,

Instead of setting the Static variable (myCoName) to a string, which
defaults to "", you could set it to a variant, which defaults to NULL.
Then, the first time you call the function, without a parameter, it will
return a NULL. Oh, BTW, you also have to change the declaration of the
function to Variant, so that you can pass the NULL value back.

Public Function fnCoName(Optional CoName as Variant = NULL) as Variant

Static myCoName as Variant

if NOT IsNull(CoName) then myCoName = CoName
fnCoName = myCoName

End function

HTH
Dale

TC said:
Dale
Thank you that helped me find the error. It was a simple typo.
One last question when I open one one of the forms that calls the
parameter
query is it possible for the the query to return a null value similar to
when
you open the form for the first time? This way all of the fields are blank
upon reopening the form?
Thanks
TC

Dale Fye said:
TC,

How many columns do you have in your combo box? I usually have an ID
field,
and a text field. If you have two, and the bound column is #1 (ID) then
you
would need to change the AfterUpdate code to read:

Call fnCoName(me.cboCompanyName.column(1)

If that is not it, lets test to make sure the function is working
properly.
Open the VB Editor and display the Immediate window.

1. Type: ?fnCoName("Test")
2. you should see: Test
3. Type: ?fnCoName
4. you should see: Test

Now open your application and select a company from one of the combo
boxes
on either form.
Go back to the immediate window and type: ?fnCoName
You should see the value of the company name that was passed by your
AfterUpdate event.

HTH
Dale


TC said:
Dale,
Please ignore my previous post I had an error in the Module code.
However
I
can now select the company from the cbo but the query is not returning
any
results.
AfterUpdate Code:
Private Sub cboCompanyName_AfterUpdate()
Call fnCoName(Me.cboCompanyName)
End Sub
Query:
SELECT tblCustomer.CustomerID, tblCustomer.[Company Name],
tblCustomer.First, tblCustomer.Last, tblCustomer.Street1,
tblCustomer.Street2, tblCustomer.City, tblCustomer.State,
tblCustomer.Zip,
tblCustomer.Officephone, tblCustomer.Cellphone, tblCustomer.Email,
tblCustomer.WebPage, tblCustomer.Notes
FROM tblCustomer
WHERE tblCustomer.[CompanyName] = fnCoName();

Any Ideas?
Thank you for your help
TC

:

Dale
I am getting a Compile Error: "Wrong number of arguments" for the
after
update event.
Any ideas?

Private Sub cboCompanyName_AfterUpdate()
fnCoName (Me.cboCompanyName)
End Sub

:

TC,

Since you cannot use global variables in a query, that really won't
work.
The way I handle this is to create a function in a code module (not
a
forms
class module). The function would look something like:

Public Function fnCoName(Optional CoName as Variant = NULL) as
String

Static myCoName as string

if NOT IsNull(CoName) then myCoName = CoName
fnCoName = myCoName

End function

The way this function works is that if you pass it a value, it sets
the
myCoName variable in the function, and returns that value to the
calling
code. Since this (myCoName) is a static variable, it retains its
value
between function calls. If you don't pass the function a value, it
returns
the value that is already stored in myCoName.

Now, in the CompanyName combo boxes AfterUpdate event, enter the
following
code:

Private Sub cbo_CompanyName_AfterUpdate

Call fnCoName(me.cbo_CompanyName)
'or you could use
'fnCoName me.cbo_CompanyName

End Sub

Then, in your query, change the where clause to:

WHERE tblCustomer.[Company Name] = fnCoName()

Now, you can use this value anywhere in your application.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have two separate forms that are based on the same parameter
query,
frmSOR
and frmQuote. I am using the following criteria for the query
WHERE (tblCustomer.[Company Name]=forms!frmQuote!CompanyName
On both of the forms CompanyName is an unbound combobox.

I can not figure out how to open just the form frmSOR and not get
the
"Set
Parameter" box open without having to have frmQuote open.

I have looked through many post and I think the easiest solution
is
to some
how set the CompanyName field as a Public string in a module. But
I
am not
sure how to do this or if this is even the correct solution.

Any help would be much appreciated.
Thanks
TC
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Control on subform / combo box requesting parameter 0
Can an IIf Statement return more than one value 11
Query Parameter using multiple options 0
Quad Cascading ComboBoxes 3
Parameter Value error in a Subform 0
Access Invalid bracketing of parameter name 1
Main Form and two Subforms 2
Modify Parameter Query 1

Top