query with value from other form

[Guest]

Heaving a form with several buttons I do call some other forms with telephone
numbers etc. Eacht called form uses a query over the table contacts filtering
all contacts by ContactTypeID. I do have also 1 list with all contacts
without any kind of filtering.
Within the form with buttons now I have defined for eacht button a Tag
corresponding the value for the several ContactTypeID but I can not manage to
open only one Telephoneform using this Tag.
fe. Button1 Tag=1 Button2 Tag=2 >> should open the same form Telephone
however this form should use a query to filter all contacts with the
corresponding Tag value.

Any kind of help would be very fine.

Harry

 

[Guest]

Harry,

If you want to use the tag then you could use a querydef like follows:

Create a regular query with the values which you need with the parameter set
to 1.
Go to the query type button on the left upper side of the query pane. Select
the SQL option. Copy the select statement created by Access. Save the query
giving it an appropriate name (for now let's just call it query1.

Now go to the VBA window (e.g. behind the button which you use to open the
form) and type the following

dim sSQL as string

sSQL= "Select...." (here you paste the statement you've copied).

Now change the Where statement to: Where telephone=" & me.tag
now give the SQL statement back to the query like follows

Currentdb.Querydefs("Query1").SQL=sSQL

If the code has come this far you can check the query by opening it in
designview to look at the result.

Now use this query as a recordsource for your form...

The sSQL statement might look something like this:

sSQL="SELECT telephone, address, email, etc " & _
"FROM [tablename] WHERE TelephoneID= " & me.tag

Hope this helps.

Maurice

 

[Guest]

Thanks Maurice,

It all works fine!

Greetings,
Harry

Harry,

If you want to use the tag then you could use a querydef like follows:

Create a regular query with the values which you need with the parameter set
to 1.
Go to the query type button on the left upper side of the query pane. Select
the SQL option. Copy the select statement created by Access. Save the query
giving it an appropriate name (for now let's just call it query1.

Now go to the VBA window (e.g. behind the button which you use to open the
form) and type the following

dim sSQL as string

sSQL= "Select...." (here you paste the statement you've copied).

Now change the Where statement to: Where telephone=" & me.tag
now give the SQL statement back to the query like follows

Currentdb.Querydefs("Query1").SQL=sSQL

If the code has come this far you can check the query by opening it in
designview to look at the result.

Now use this query as a recordsource for your form...

The sSQL statement might look something like this:

sSQL="SELECT telephone, address, email, etc " & _
"FROM [tablename] WHERE TelephoneID= " & me.tag

Hope this helps.

Maurice

Heaving a form with several buttons I do call some other forms with telephone
numbers etc. Eacht called form uses a query over the table contacts filtering
all contacts by ContactTypeID. I do have also 1 list with all contacts
without any kind of filtering.
Within the form with buttons now I have defined for eacht button a Tag
corresponding the value for the several ContactTypeID but I can not manage to
open only one Telephoneform using this Tag.
fe. Button1 Tag=1 Button2 Tag=2 >> should open the same form Telephone
however this form should use a query to filter all contacts with the
corresponding Tag value.

Any kind of help would be very fine.

Harry

 

[Guest]

Maurice,
Could you help me out a little bit further?
some way or the other after putting a line
ORDER BY [Q-Contacts].CompanyName I get an error that an end of statement
expression is expected.
However when adjusting the query with this statement (within design view)
all works fine.

Harry

Harry,

If you want to use the tag then you could use a querydef like follows:

Create a regular query with the values which you need with the parameter set
to 1.
Go to the query type button on the left upper side of the query pane. Select
the SQL option. Copy the select statement created by Access. Save the query
giving it an appropriate name (for now let's just call it query1.

Now go to the VBA window (e.g. behind the button which you use to open the
form) and type the following

dim sSQL as string

sSQL= "Select...." (here you paste the statement you've copied).

Now change the Where statement to: Where telephone=" & me.tag
now give the SQL statement back to the query like follows

Currentdb.Querydefs("Query1").SQL=sSQL

If the code has come this far you can check the query by opening it in
designview to look at the result.

Now use this query as a recordsource for your form...

The sSQL statement might look something like this:

sSQL="SELECT telephone, address, email, etc " & _
"FROM [tablename] WHERE TelephoneID= " & me.tag

Hope this helps.

Maurice

Heaving a form with several buttons I do call some other forms with telephone
numbers etc. Eacht called form uses a query over the table contacts filtering
all contacts by ContactTypeID. I do have also 1 list with all contacts
without any kind of filtering.
Within the form with buttons now I have defined for eacht button a Tag
corresponding the value for the several ContactTypeID but I can not manage to
open only one Telephoneform using this Tag.
fe. Button1 Tag=1 Button2 Tag=2 >> should open the same form Telephone
however this form should use a query to filter all contacts with the
corresponding Tag value.

Any kind of help would be very fine.

Harry

 

[Guest]

Hi Harry,

Good to hear it works for you. As far as the ORDER BY clause it would help a
little if you could past your SQL-statement here so that I can figure out
what the problem could be. It might be that you've forgotten parentheses....

So past your statement and i'll be glad to help you out here..
(You can replace tablenames with fictious names if you have companydata)

Maurice

Maurice,
Could you help me out a little bit further?
some way or the other after putting a line
ORDER BY [Q-Contacts].CompanyName I get an error that an end of statement
expression is expected.
However when adjusting the query with this statement (within design view)
all works fine.

Harry

Harry,

If you want to use the tag then you could use a querydef like follows:

Create a regular query with the values which you need with the parameter set
to 1.
Go to the query type button on the left upper side of the query pane. Select
the SQL option. Copy the select statement created by Access. Save the query
giving it an appropriate name (for now let's just call it query1.

Now go to the VBA window (e.g. behind the button which you use to open the
form) and type the following

dim sSQL as string

sSQL= "Select...." (here you paste the statement you've copied).

Now change the Where statement to: Where telephone=" & me.tag
now give the SQL statement back to the query like follows

Currentdb.Querydefs("Query1").SQL=sSQL

If the code has come this far you can check the query by opening it in
designview to look at the result.

Now use this query as a recordsource for your form...

The sSQL statement might look something like this:

sSQL="SELECT telephone, address, email, etc " & _
"FROM [tablename] WHERE TelephoneID= " & me.tag

Hope this helps.

Maurice

Heaving a form with several buttons I do call some other forms with telephone
numbers etc. Eacht called form uses a query over the table contacts filtering
all contacts by ContactTypeID. I do have also 1 list with all contacts
without any kind of filtering.
Within the form with buttons now I have defined for eacht button a Tag
corresponding the value for the several ContactTypeID but I can not manage to
open only one Telephoneform using this Tag.
fe. Button1 Tag=1 Button2 Tag=2 >> should open the same form Telephone
however this form should use a query to filter all contacts with the
corresponding Tag value.

Any kind of help would be very fine.

Harry