Combo Box: double selection required 1

[DajTwo]

I looked at faq702-4289 and although I understand the concept, I cannot make it work in my application.
(Access 2003 front end-back end config)

The data to show on the form is generated by the query qry_frm_audit_info and the record is to be selected as follow

CCAN is the unique ID.
audit_no is an autogenerated #

The Combo box code is now as follow:
(User select the custoemr name and the matching CCAN select the reorf on the form)

RowSource: SELECT qry_frm_audit_info.cust_name, qry_frm_audit_info.ccan FROM qry_frm_audit_info GROUP BY qry_frm_audit_info.cust_name, qry_frm_audit_info.ccan ORDER BY qry_frm_audit_info.cust_name;

AfterUpdate(): Private Sub Combo44_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[cust_name] = '" & Me![Combo44] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

As it stands now, if a CCAN has multiple audit_#, only the 1st one shows on the form.

Possible solution I think of but cant program:

Include in the RowSource of the combobox qry_frm_audit_info.cust_name.audit_# then the use will be able to see the audit_no in the combobox (3 columns) and

and amend the following to include audit_no?

rs.FindFirst "[cust_name] = '" & Me![Combo44] & "'"

Is this possible? If not then I will have to go to 2 combo box where the 1st one will select the CCAN and the 2nd one the audit_no.

Any ideas are very welcomed.

Thanks in advance

 

[boblarson]

I would go with the two step method.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[DajTwo]

Thanks Bob,

As I stated in the 1st part of the post, I cannot make the 2 step work. Can you provide me with an overall structure that would work ? FAQ 702-4289 did not work for me.

Thanks

 

[TheAceMan1]

How are ya DajTwo . . .

To see all [blue]audit_#[/blue] per [blue]CCAN[/blue], you need to [purple]filter[/purple] ... [blue]not findfirst![/blue]

[blue]   Me.Filter = "[CCAN] = [red][b]'[/b][/red]" & Me!Combo44.Column(1) & "[red][b]'[/b][/red]"
   Me.FilterOn = True[/blue]

If CCAN is numeric drop the two single quotes in [red]red[/red].

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

AceMan

You are making me think that I should hire your services to solve my problems for this darn project dropped on my lap. Seriously!!! If you are available, please let me know.


So if I understand. The 1st combo box RowSource select ccan/name. The AfterUpdate list all the audit_# for that ccan/name. The 2nd combo box should list what has been selected in Combobox1 allowing the user to select the audit_# listed for that ccan/name.

Please let me know if my code is in the vicinity of being on the right track

1s combo box:

RowSource: SELECT qry_frm_audit_info.cust_name, qry_frm_audit_info.ccan FROM qry_frm_audit_info GROUP BY qry_frm_audit_info.cust_name, qry_frm_audit_info.ccan ORDER BY qry_frm_audit_info.cust_name;


AfterUpdate(): Private Sub Combo44_AfterUpdate()
' Find all audit_# record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
Me.Filter = "[CCAN] = '" & Me!Combo44.Column(1) & "'"
Me.FilterOn = True
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

2nd combo box

RowSource: SELECT qry_frm_audit_info.cust_name, qry_frm_audit_info.ccan, qry_frm_audit_info.audit_# FROM qry_frm_audit_info GROUP BY qry_frm_audit_info.cust_name, qry_frm_audit_info.ccan, qry_frm_audit_info.audit_# WHERE qry_frm_audit_info.audit = [forms
![audit_info]![combo44] ORDER BY qry_frm_audit_info.audit_;

Private Sub combo50_GotFocus()
If Len(Trim(Nz(combo44, "") & "")) = 0 Then
MsgBox "Please Specify Customer first"
combo44.SetFocus
Else
combo50.Requery
End If

 

[TheAceMan1]

DajTwo . . .

If a [blue]CCAN[/blue] can have more than one [audit_#] then were looking at a normalization problem (at least thru the SQL's you provided). To nail this, some assemblence of your table structure is needed.

Post back table names, primarykey names and their relationships (whose the parent or child), and any field names related to this post.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Aceman,

Thanks for the reply.

Tables:

tbl_customer: ccan is the Unique Key, cust_name etc..
tbl_audit: audit_sub_# is the Unique Key (autogenerated), audit_#, ccan, audit_status etc..

Each CCAN (customer) will have several audit_#.
Each audit_# may has it's unique audit_sub_#

From a business prespective, a client [ccan] will be audited/visited several times [audit_#], and each of the visits has it's own number [audit_sub_#].

Again, great thanks for your help. My offer stands.

 

[DajTwo]

Sorry, I made some typos and it was not clear. Very sorry, no disrecpect intended.

Tables:

tbl_customer: ccan is the Unique Key with different fields indluding cust_name etc..

tbl_audit: audit_sub_# is the Unique Key (autogenerated), ccan, audit_# which is autogenerated, audit_status etc..

Each CCAN (customer) is unique
Audit_# are autogenerated created from time to time by the user. This is to store information at that level. Each ccan will have several audit_#.

Each audit_# has it's unique audit_sub_# (autogenerated) in order to capture the statuses of each audit.

From a business prespective, a client [ccan] will be audited/visited several times [audit_#], and each of the visits has it's own number [audit_sub_#].

I hope this is clearer.

 

[TheAceMan1]

Roger That DajTwo . . .

The following is based on the condition that you have a [purple]one to many relationship[/purple] based on [blue]CCAN[/blue], with [blue]tbl_Customer[/blue] as the parent and [blue]tbl_Audit[/blue] as the child.

To verify your goal: you want to select/goto a customer and view all audits for that customer.

Problem: You made it hard for yourself by trying to view data from two tables in one form.

What you need: A mainform based on [blue]tbl_Customer[/blue] with subform based on [blue]tbl_Audit[/blue]. With this alone (no comboboxes) you'll see that as you navigate customers their respective audits will show in the subform (your more than 99% there already!).

To polish it off, all you need is a combo in the main form to navigate the customers. You need the combo only because the mainform will be in [blue]Single View[/blue] and the combo saves single stepping.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

You are right stating that I am 99% there already.

There is a slight misunderstanding on the goal.

Yes, I need to see all the audits for that client and that is done by the combo box and since a CCAN can have multiple audit, the user needs to be able to select a
"specific audit #" to view the data in a Form view.

That is the reason of my original request where I discussed the option of 2 combo boxes where as per your 1st reply, the 1st combo would be a filter.

Thanks AceMan for your time and efforts.

 

[TheAceMan1]

Roger That DajTwo . . .

No problemo! (now that I know table structure!).

We can conquer the comboxes once the mainform with subform is established. Is this so .... or do you need help?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Aceman

Currently, the main form (form view) is made with a combo box that selects the FIRST CCAN as per the following code (see 1st post)

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[cust_name] = '" & Me![Combo44] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

In order to have the 2 combo option to select the audit_# and if i understand your last post, I need to have a subform IN the main form with the same record source as the main form?

 

[TheAceMan1]

DajTwo . . .

No ... No ... No! The main and subform need to be based sepereately on the tables you provided: [blue]tbl_Customer[/blue] and [blue]tbl_Audit[/blue] . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

That is to say ... the mainform is based on tbl_customer and the subform is based on tbl_Audit . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Aceman,

I am in a front end back end config. I was told it was preferable to link with queries and not tables and that is what I did so far in my project..

Am I to setup queries to feed the main form and the subform?

 

[TheAceMan1]

[blue] ... I was told it was preferable to link with queries and not tables and that is what I did so far in my project...[/blue]

This has nothing to do with wether the DB is split or not, but yes I agree ... only in the fact that queries are much more versatile then tables themselves (the only reason). You can sort, perform sum queries, add custom fields, and a host of other things, compared to a table alone where you can do nothing! Consider ... if you at least want to sort, its gonna take a query or equivalent SQL that does so. A table is a table and thats all you get! ... Got It!

[blue]Am I to setup queries to feed the main form and the subform?[/blue]

Yes! ... because at a minimum I'm sure you at least want to sort as far as the form views are concerned.

Again ... Yes . . . [blue]Do It![/blue] ... Make the queries and the mainform/subform . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Thanks Aceman

I will do this 1st thing tomorrow morning at work. Will let you know.

Then you will be able to make me understand how the double combo works!!!!

Thanks again!!

 

[DajTwo]

Aceman,

I created forms as suggested.

frm_main (form view):
RecourdSource qry_main_cst
qry_main_cst: Select [ccan], [customer_name] from tbl_customer

frm_main_sub (form view):
SourceObject: from_main_sub

RecourdSource: SELECT qry_main_aud.ccan, qry_main_aud.audit_no, qry_main_aud.[audit_sub_#], qry_main_aud.audit_status, qry etc...... FROM qry_main_aud;

I have not yet selected the Link Child Fields and the
Link Master Field in the subform.

Let the light shine on me !!!!

 

[TheAceMan1]

DajTwo . . .

The link master/child fields should be CCAN (for both).

When linked you should be able to parse customers and view all audit_# in the subform. Post back when this is so . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Aceman,

I can scoll through all customer selected in the query and view all audits for that customer.

How can I make the subform viewed as a singleform and not as a 'spreadsheet' view?

Thanks