Afterupdate to populate form

[Angel_1]

I have 1 form which i want to use for viewing (and adding) employee details.
I have a combo box in the header of the form, cboSchemeID. Basically i want
to choose the scheme from the dropdown list cboSchemeID and then have it only
show employees from that scheme, sounds simple!
All of the other controls on the form are populated by a query
QryCompanyFormDetails. The records display fine scrolling thorough using the
navigation buttons but not using the combo box. Any help would be much
appreciated. Thanks

 

[tina]

yes, it's simple enough. let's say your form is named MyForm, and the
combobox control in the header is named, as you say, cboSchemeID. since the
form is bound to a query already, just open the query in design view. in the
"scheme" field's column, in design view, go to the Criteria line and enter
the following, as

[Forms]![MyForm]![cboSchemeID] Or [Forms]![MyForm]![cboSchemeID] Is Null

the above goes all on one line, and you need to substitute the correct form
name, of course.

on the form, add a command button (you could caption it "Filter" or "Find"),
and add code to the button's Click event procedure, as

Me.Requery
Me!cboSchemeID = Null

to filter the records to employees with a particular scheme, choose a scheme
from the combo box control and click the Filter button. the form's
underlying query is re-run, to return only the appropriate records. click
the button again to see all records.

hth

 

[Steve Schapel]

Angel,

Here are a couple of suggestions...

1. In your QryCompanyFormDetails query, in the criteria of the SchemeID
field, put a reference to the combobox, using syntax such as...
[Forms]![NameOfYourForm]![cboSchemeID]
Then, on the After Update event of the cboSchemeID combobox, use code
like this...
Me.Requery

2. Leave the QryCompanyFormDetails query as it is, and on the After
Update event of the cboSchemeID combobox, use code like this...
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID

 

[Angel_1]

Hi Thanks for your advice but i still cant get it to work, any ideas. I
meant to say that on the main form [CompanyPage] there is a tab control page,
(like in northwind).
Any advice how to fix this or even ideas on how to make it simpler, thanks.

Angel,

Here are a couple of suggestions...

1. In your QryCompanyFormDetails query, in the criteria of the SchemeID
field, put a reference to the combobox, using syntax such as...
[Forms]![NameOfYourForm]![cboSchemeID]
Then, on the After Update event of the cboSchemeID combobox, use code
like this...
Me.Requery

2. Leave the QryCompanyFormDetails query as it is, and on the After
Update event of the cboSchemeID combobox, use code like this...
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID

--
Steve Schapel, Microsoft Access MVP

I have 1 form which i want to use for viewing (and adding) employee details.
I have a combo box in the header of the form, cboSchemeID. Basically i want
to choose the scheme from the dropdown list cboSchemeID and then have it only
show employees from that scheme, sounds simple!
All of the other controls on the form are populated by a query
QryCompanyFormDetails. The records display fine scrolling thorough using the
navigation buttons but not using the combo box. Any help would be much
appreciated. Thanks

 

[Steve Schapel]

Angel,

The fact you are using a Tab Control sould not make any difference.
Unless you are talking about a subform... in which case you'd better
tell us how this is set up, and whether the cboSchemeID combobox is on
the subform or the main form, and what else is on the main form, and all
that.

"Can't get it to work" doesn't really give us an awful lot to go on.
Can you say what you tried so far and what doesn't work, and what in
fact is happening? Can you also post back with the SQL view of the
query that the form is based on (QryCompanyFormDetails?).

We are assuming the cboSchemeID combobox is unbound (i.e. you will see
its Control Source property is blank), right?

 

[Angel_1]

Sorry,
I have one form, CompanyPage which has cboSchemeID in the header and the tab
control in the detail section. The Recourd Souce of CompanyPage is
QryCompanyFormDetails, cboSchemeID is unbound, the Row Source is
QrySchemeIDSelect.
QryCompanyFormDetails returns all staff and details including duplicates
because some of them belong to more than one sheme. The SQL:
SELECT QryCompanyFormDetails.[Scheme ID]
FROM QryCompanyFormDetails
GROUP BY QryCompanyFormDetails.[Scheme ID]
ORDER BY QryCompanyFormDetails.[Scheme ID];

QrySchemeIDSelect returns a list of all the available Schemes to choose
from. I have Grouped by SchemeID from the QryCompanyFromDetails.
The tables am using are Employee Details and also a link table
Employee-Scheme(caus some employees belonging to multiple Schemes), this just
list all Employee IDs and there schemes.
I'm sorry if this is a bit confusing. Thanks very much for all your help.

Angel,

The fact you are using a Tab Control sould not make any difference.
Unless you are talking about a subform... in which case you'd better
tell us how this is set up, and whether the cboSchemeID combobox is on
the subform or the main form, and what else is on the main form, and all
that.

"Can't get it to work" doesn't really give us an awful lot to go on.
Can you say what you tried so far and what doesn't work, and what in
fact is happening? Can you also post back with the SQL view of the
query that the form is based on (QryCompanyFormDetails?).

We are assuming the cboSchemeID combobox is unbound (i.e. you will see
its Control Source property is blank), right?

--
Steve Schapel, Microsoft Access MVP

Hi Thanks for your advice but i still cant get it to work, any ideas. I
meant to say that on the main form [CompanyPage] there is a tab control page,
(like in northwind).
Any advice how to fix this or even ideas on how to make it simpler, thanks.

 

[Steve Schapel]

Angel,

Thanks for the further explanation.

Well, I'm afraid that takes us right back to the beginning... this was
pretty much what I had assumed .

I expect you tried both of the methods I initially suggested? So, what
actually happens if you put this code on the After Update event of the
cboSchemeID combobox?...
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID

As far as I can see, this should work finew - the only assumption I am
making that could influence this is that Scheme ID is included as a
field in the QryCompanyFormDetails query. So, what happens... nothing
changes? form goes blank? error message? something else?

 

[Angel_1]

Hi,
Yes i did try both methods you suggested with no luck
When i add the code to cboSchemeID an error comes up saying Compile Error,
Sub or function not defined.

Angel,

Thanks for the further explanation.

Well, I'm afraid that takes us right back to the beginning... this was
pretty much what I had assumed .

I expect you tried both of the methods I initially suggested? So, what
actually happens if you put this code on the After Update event of the
cboSchemeID combobox?...
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID

As far as I can see, this should work finew - the only assumption I am
making that could influence this is that Scheme ID is included as a
field in the QryCompanyFormDetails query. So, what happens... nothing
changes? form goes blank? error message? something else?

--
Steve Schapel, Microsoft Access MVP

Sorry,
I have one form, CompanyPage which has cboSchemeID in the header and the tab
control in the detail section. The Recourd Souce of CompanyPage is
QryCompanyFormDetails, cboSchemeID is unbound, the Row Source is
QrySchemeIDSelect.
QryCompanyFormDetails returns all staff and details including duplicates
because some of them belong to more than one sheme. The SQL:
SELECT QryCompanyFormDetails.[Scheme ID]
FROM QryCompanyFormDetails
GROUP BY QryCompanyFormDetails.[Scheme ID]
ORDER BY QryCompanyFormDetails.[Scheme ID];

QrySchemeIDSelect returns a list of all the available Schemes to choose
from. I have Grouped by SchemeID from the QryCompanyFromDetails.
The tables am using are Employee Details and also a link table
Employee-Scheme(caus some employees belonging to multiple Schemes), this just
list all Employee IDs and there schemes.
I'm sorry if this is a bit confusing. Thanks very much for all your help.

 

[Steve Schapel]

Angel,

This is what it should look like...

In design view of the form, select the cboSchemeID combobox, and look at
the Properties. In the After Upate property, it should say:
[Event Procedure]
Click on the little ellipsis [...] button to the right, which will open
the VB Editor window. You should see like this:
Private Sub cboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID
End Sub

If your situation is any different from this, please supply details.

 

[Angel_1]

Hi
I really appreciate your help, but i still cant get it to work.
I get an error message saying Compile Error, Sub or funciton not defined.
WHERE is highlighted on the VB Editor Window.

Angel,

This is what it should look like...

In design view of the form, select the cboSchemeID combobox, and look at
the Properties. In the After Upate property, it should say:
[Event Procedure]
Click on the little ellipsis [...] button to the right, which will open
the VB Editor window. You should see like this:
Private Sub cboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID
End Sub

If your situation is any different from this, please supply details.

--
Steve Schapel, Microsoft Access MVP

Hi,
Yes i did try both methods you suggested with no luck
When i add the code to cboSchemeID an error comes up saying Compile Error,
Sub or function not defined.

 

[Steve Schapel]

Angel,

This shouls all be in one line....
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID

It occurs to me that your newsreader may be putting in a line wrap and
you have copied that to your code?

If not, please copy/paste the code from your VBE into your reply here,
let's have a look.

 

[Angel_1]

Hi Steve,
I copy and pasted what you wrote but access automatically added the extra "
I have tried everything, drivin me nuts now. (Scheme ID is correct with the
space)
thanks alot

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails"
WHERE
Scheme ID = " & Me.CboSchemeID"
End Sub

 

[Douglas J. Steele]

Steve's statement was intended to be typed on a single line, but word-wrap
makes it difficult to see that sometimes.

I've split the statement into shorter lines, with concatenation characters
are the end of each line.

The first two lines in the routine should end & _ (that's ampersand, space,
underscore),


Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"Scheme ID = " & Me.CboSchemeID
End Sub

 

[Steve Schapel]

In addition to Doug's comments, Angel, you will need to enclose the
Scheme ID in []s to cater to the space...

Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"[Scheme ID] = " & Me.CboSchemeID

 

[Douglas J. Steele]

Argh. It's always the simple ones I miss!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

In addition to Doug's comments, Angel, you will need to enclose the Scheme
ID in []s to cater to the space...

Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"[Scheme ID] = " & Me.CboSchemeID

--
Steve Schapel, Microsoft Access MVP

Steve's statement was intended to be typed on a single line, but
word-wrap
makes it difficult to see that sometimes.

I've split the statement into shorter lines, with concatenation
characters
are the end of each line.

The first two lines in the routine should end & _ (that's ampersand,
space,
underscore),


Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"Scheme ID = " & Me.CboSchemeID
End Sub

 

[Steve Schapel]

Hey Doug, some people have more experience than others with spaces in
field names ;-) .

 

[Angel_1]

Hi Douglas,
I tried what u suggested and i get a Run time error 3705, Syntax error
(missing operator) in query expression 'Scheme ID = Office'

 

[Douglas J. Steele]

As Steve pointed out yesterday, since you've got a space in the field name,
you need to enclose it in square brackets:

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"[Scheme ID] = " & Me.CboSchemeID
End Sub

 

[Angel_1]

Ok, thanks to you both, i think i'm nearly there now.
It now says enter Parameter value with the name of the selected scheme. :-(

 

[Angel_1]

Ok, thanks to you both, i think i'm nearly there now.
It now says enter Parameter value with the name of the selected scheme. :-(

As Steve pointed out yesterday, since you've got a space in the field name,
you need to enclose it in square brackets:

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"[Scheme ID] = " & Me.CboSchemeID
End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,
I tried what u suggested and i get a Run time error 3705, Syntax error
(missing operator) in query expression 'Scheme ID = Office'