Yes, that would be a problem. Ok, lets do something else. We're going to
programmatically set the rowsource of the combo box so that if you are on a
new record, then the combo will be filtered to only show the active clients,
and all existing records will show ALL clients.
1. Open your subform in design view
2. Bring up its properties page
3. Click on your combobox
4. Go to the Row Source line
5. Delete all the text on the line
(don't open the query builder)
6. Leave all of the other settings as specified in last post
7. Click on blank area off of the form
<On the form's property sheet:>
8. Click the Events tab
9. Click "On Current"
10. Click drop down| Select "Event Procedure"
11. Click ellipse(...) to launch editor
You'll then see:
-----------------------------
Private Sub Form_Current()
End Sub
-----------------------------
12. Copy all of the text below at once
13. Paste it into the blank line
-----------------------------
Private Sub Form_Current()
<You'd paste copied code here>
End Sub
-----------------------------
'copy everything below this line
'and don't forget to change ClientStatus
'to the real name of your field
dim allSQL as String 'unfiltered
dim actSQL as String 'filtered
allSQL = "SELECT CLIENt.[Client Id], " & _
"CLIENt.[First Name] & " " & [Surname] As Client " & _
"FROM CLIENt " & _
"ORDER BY CLIENt.[First Name], CLIENt.Surname;"
actSQL = "SELECT CLIENt.[Client Id], " & _
"CLIENt.[First Name] & " " & [Surname] As Client " & _
"FROM CLIENt " & _
"WHERE(((CLIENt.[ClientStatus]) = "Y")) " & _
"ORDER BY CLIENt.[First Name], CLIENt.Surname;"
If Me.NewRecord Then 'show only active clients
Me.[YourCombo].RowSource = actSQL
Else 'not new record, show all clients
Me.[YourCombo].RowSource = allSQL
End if
'end copy
----------------------------------------------
14. Click Save button
15. Go to Menu bar
16. Click Debug|Compile
if you get an error, copy the highlighted line of code and paste it into a
reply to this post
otherwise, you don't get any errors, close the editor
17. Close your subform
That should do it. If not, then you also might have to paste all of the same
code into the Form_Load procedure.
Post back if you need more help, although I won't get to it until tomorrow.
Gotta sleep sometimes.
HTH,
Brian
Andrew C said:
Brian
That works great that it only gives me the active clients. the problems it
creates is that if there was an existing record with the clients name there
it disappears and the contact form is filtered to only active clients rather
than all clients
Brian Bastl said:
Hi Andrew,
answers in line:
No its has its own field which gets entered with a Y or N. But i am
thinking of changing it over.
Is this a Yes/No field, or is it simply a text field? If the latter, then
I'd personally change it to a Yes/No field. But that's up to you.
For the sake of this example, let's call your existing field [ClientStatus]
where the values are either a Y or N "typed-in".
Your combo's rowsource would look like:
SELECT CLIENt.[Client Id], CLIENt.[First Name] & " " & [Surname] As Client
FROM CLIENt
WHERE(((CLIENt.[ClientStatus]) = "Y"))
ORDER BY CLIENt.[First Name], CLIENt.Surname;
Is your Client table really spelled "CLIENt"?
could you tell me where to insert the code you mentioned.
to insert the SQL, open up your subform in design view
open the form's property sheet from the menu bar:
View|Properties
1. Click the "All" tab
2. Click once on "Row Source"
3. Click the ellipse (...) to launch query builder
(A "Show Table" window should pop up)
4. Select Client table from list and click the Add button
5. Select ClientID from the list and drag it into the first column
6. in the field name of the second column, type: Client:=[First Name] & " "
& [Surname]
7. Select [YourClientStatusField] and drag it into the next empty column
a. uncheck it
b. in the criteria line, type: Y
8. Select [First Name] from list and drag it into the next empty column
a. uncheck it
b. in the sort line, type: ascending
9. Select [Surname] from list and drag it into the next empty column
a. uncheck it
b. in the sort line, type: ascending
To view the actuall SQL for the query
go to the menu bar| View| SQL
10. Close the query to return to the properties sheet
11. Set Column Count to 2
12. Set Column Widths to 0";2"
13. Set Bound Column to 1
14. Column Headings = No
15. Close Properties sheet
16. Close form and save changes
That's it! Your done with your combo.
Sorry but im not an advance user of access
No reason to apologize in the least. Everyone has to start somewhere.
Post back if you need further assistance.
HTH,
Brian
:
Andrew,
Do you differentiate between Active and inactive clients with a boolean
field, i.e. checkbox? If so, just add that to the criteria of the
rowsource
for the combo box
something like WHERE(((YourTable.YourBooleanField) = -1))
Brian
Hi
I have a from with a Combo box where you select a staff members name
and
then a sub form which shows the records associated to that staff
member.
On the subform i have another combo box listing clients names and
other
information.
What i want on the subform is to show all records relating to that
staff
member. But when they go to enter a new record on that subform they
can
only
pick from active clients.
Is this possible.
