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

 

[TheAceMan1]

DajTwo . . .

Sorry to get back so late. Been away for my birthday.Will be home tomorrow.

Independently open the subform in design view. Change the 'form view' property to "single".

See Ya! . . . . . .

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

 

[DajTwo]

Happy Birthday!!!. Not to worry, I have stuff to fix in the database.

Take it easy!!!

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

So the subform should now be in [blue]single view[/blue].

Is this correct? ... and if I'm not mistaken, the main form should be in [blue]single view[/blue] as well!

We should be ready for the combo's . . .

See Ya! . . . . . .

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

 

[DajTwo]

Yes, both are

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[DajTwo]

Aceman:

Let me know if I am on the right track here. I made some research while you were away and came up with this. The 2 combo work but I cannot select the proper records on the main form or sub form. Took me for ever to make it work.

Combo 8

Row Source SELECT qry_main_cst.cust_name, qry_main_cst.ccan FROM qry_main_cst ORDER BY qry_main_cst.cust_name;

Private Sub Combo8_AfterUpdate()

Dim sAuditSource As String

sAuditSource = "SELECT [qry_main_aud].[audit_no], [qry_main_aud].[ccan], [qry_main_aud].[audit_prepost_date] " & _
"FROM [qry_main_aud] " & _
"WHERE [ccan]= '" & Me.Combo8.Column(1) & "'"
Me.Combo10.RowSource = sAuditSource
Me.Combo10.Requery

End Sub


Combo 10

Row Source: SELECT qry_main_aud.audit_no, qry_main_aud.audit_prepost_date, qry_main_aud.ccan FROM qry_main_aud ORDER BY qry_main_aud.audit_no;



If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[DajTwo]

Aceman,

You must be in holidays.

Let me knwo when you can help me with the code of the combo boxes so I can select the proper records.

Thanks

Daj

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

Yes ... your on the right track, however, your method doesn't afford your understanding the process of control. In the following I'll refer to the controlled combox as the [blue]child[/blue], the controlling combobox as the [blue]parent[/blue].
[ul][li]To control one combobox by another we prescribe one or more fields of the [blue]childs[/blue] SQL criteria [blue]to be dependant[/blue] on the same field(s) in the [blue]parent[/blue]. Within the [blue]childs[/blue] SQL this normally takes the form:

[blue]   WHERE [[purple][B][I]DependantFieldName[/I][/B][/purple]] = Forms!FormName!ComboboxName[/blue]

[/li]
[li]Note: The above simply sets the query/SQL to show the proper dependant data. The child is not controlled by the parent yet! ... or doesn't update on parent selection.[/li]
[li]To control the [blue]child[/blue] all it takes is a simple [blue]Requery[/blue] of the child! This is usually done in the [blue]After Update[/blue] event of the [blue]parent[/blue], so that updating of the [blue]child[/blue] coincides with [blue]parent[/blue] selection.[/li]
[li]With all the above accomplished, you should have a [blue]child[/blue] dependant on a [blue]parent![/blue][/li][/ul]
Before we continue we need to touch on the use of combobox in the [blue]SQL of a query[/blue] (not SQL in VBA). We will be using SQL in query.

[blue]You can not use the [purple]Column[/purple] property in the SQL of a query. This means, to use the combobox directly the [purple]the desired field should occupy the first column[/purple]. Any other column within the combobox will have to be returned by a function.[/blue]

This simply dictates a little more thought on your combo's first column.

Also be aware:

[blue]The column that shows in the textbox portion of a combobox is the first non-zero width column in the [purple]Column Widths[/purple] property.

For reference note that column index starts at zero[/blue]

Ok ... lets get on with it. For [blue]combo10:[/blue]

[blue][purple][b]RowSource should be:[/b][/purple]

SELECT audit_no, audit_prepost_date, ccan WHERE [ccan] = [Forms]![frm_Main]![Combo8] FROM [purple][b]tbl_Audit[/b][/purple] ORDER BY audit_no;
[purple]Note Combo8 doesn't use the column property as you gonna insure its the 1st column![/blue]

[purple][b]Properties:[/b][/purple]
Control Source = none/empty
Column Count = 3
Column Widths = 1";1";0"

[purple][b]AfterUpdate event should be:[/b][/purple]

   Dim frm As Form
   
   Set frm = [frm_main_sub].Form
   frm.Recordset.FindFirst "[audit_no] = " & Me!Combo10
   Set frm = Nothing[/blue]

For [blue]combo8:[/blue]

[blue][purple][b]RowSource should be:[/b][/purple]

Row Source SELECT ccan, cust_name FROM [purple][b]tbl_Customer[/b][/purple] ORDER BY cust_name;

[purple][b]Properties:[/b][/purple]
Control Source = none/empty
Column Count = 2
Column Widths = 0";1"

[purple][b]AfterUpdate event should be:[/b][/purple]

   Dim frm As Form
   
   Set frm = [frm_main_sub].Form
   frm.Recordset.FindFirst "[audit_no] = " & Me!Combo10
   Set frm = Nothing[/blue]

See Ya! . . . . . .

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

 

[TheAceMan1]

DajTwo . . .

Hit submit to soon so disregard [blue]Combo8[/blue] in my prior post. Will continue here.

[blue]For combo8:
[purple][b]RowSource should be:[/b][/purple]

SELECT ccan, cust_name FROM [purple][b]tbl_Customer[/b][/purple] ORDER BY cust_name;

[purple][b]Properties:[/b][/purple]
Control Source = none/empty
Column Count = 2
Column Widths = 0";1"

[purple][b]AfterUpdate event should be:[/b][/purple]

   [frm_main_sub].Form!Combo10.Requery
   Me.Recordset.FindFirst "[CCAN] = " & Me!Combo8[/blue]

Let me know how ya make out! . . .

See Ya! . . . . . .

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

 

[DajTwo]

Hi Aceman,

I hope you have a good 2009!

I apologise in advance for any stupid errors I can make. Being in the office on jan 1st shoudl show that at least I am motivated to get this thing working..


1st reaction is that the combo boxes RowSources are from tables and not queries. Being not sure if it makes a difference I tried with both tables and queries as I will need to limit the records listed in the combo boxes.

To remain consistant with the rest of the database, I made a change to the name of the main form (frm_main) to audit_info and frm_main_sub to audit_subform for the subform .

Question: the record source on the forms audit_info and audit_subform will be audit_no?



I get these 2 errors if I use either tables or queries as RowSource in the combo boxes.

Combo 8: The field ‘ | ‘ cannot be found. Could be that [CCAN] is a text field?
Me.Recordset.FindFirst "[CCAN] = " & Me!Combo8


Combo 10: I get the error missing operator in

'ccan WHERE [ccan] = [Forms]![audit_info]![Combo8]'
‘qry_frm_audit_sub.ccan WHERE [ccan] = [Forms]![audit_info]![Combo8]’


This is the exact replica of the code I used.

Combo8

RowSource (with tables): SELECT ccan, cust_name FROM tbl_customer ORDER BY cust_name;

RowSource (with query): SELECT qry_frm_audit_info.ccan, qry_frm_audit_info.cust_name FROM qry_frm_audit_info ORDER BY qry_frm_audit_info.cust_name;


Properties

Control Source = empty
Column Count = 2
Column widths = 0", 2"
Bound Column = 2 (?)

Private Sub Combo8_AfterUpdate()

[audit_subform].Form!Combo10.Requery
Me.Recordset.FindFirst "[CCAN] = " & Me!Combo8

End Sub



Combo 10

RowSource (with tables) : SELECT audit_no, audit_prepost_date, ccan WHERE [ccan] = [Forms]![audit_info]![Combo8] FROM tbl_audit ORDER by audit_no;

RowSource (with queries): SELECT qry_frm_audit_sub.audit_no, qry_frm_audit_sub.audit_prepost_date, qry_frm_audit_sub.ccan WHERE [ccan] = [Forms]![audit_info]![Combo8] FROM qry_frm_audit_sub ORDER by audit_no;

Properties

Control Source = empty
Column Count = 3
Column widths = 1",1",0"
Bound Column= 1 (?)

Private Sub Combo10_AfterUpdate()

Dim frm As Form

Set frm = [audit_subform].Form
frm.Recordset.FindFirst "[audit_no] = " & Me!Combo10
Set frm = Nothing

End Sub




If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

A Happy New Year to you too.

[blue]1st reaction is that the combo boxes RowSources are from tables and not queries.[/blue]

[blue]There is no query that is not based on a table![/blue] Open a query in design view and at the top in the table pane you'll see the table(s) the query is based on.

Now ... while your still in query design view, on the far left of the toolbar you'll see a combobox where [blue]you select the view[/blue] of the query. Select SQL. Do you see where I'm going with this?

[blue]The SQL of a query is the query! Its the SQL that runs when you click a query or run it from VBA!

My point is: [purple]An SQL statement, is a query![/purple][/blue]

Now ... for the recordsource of a form or combobox you could use a [blue]query name[/blue] or the [blue]SQL[/blue] of that query. But your indicating to me that you may have a normalization problem. So a major question would be:

[blue]Do the records in the [blue]tbl_Customer[/blue] comprise one name & one only per record, or can there be duplicate names?[/blue]

Also, open the proper table in design view and let me know the data type of CCAN and audit_no (text,numeric).

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

See Ya! . . . . . .

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

 

[DajTwo]

Aceman,

To answer your questions.

tbl_customer has only one (1) CCAN and one (1) corresponding cust_name. No duplicates are possible.

CCAN is text, and audit_no is numeric.

If I understand, I can base my combo box record sources on queries as the record source is an SQL statement. The sql statement will restrict the number of CCANs listed on the combo box 8. When the combo boxes and the records show up on the audit_info and the audit_subform forms, I can experience with the Record Source of Combo8.

Thanks again!!






If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[DajTwo]

As a precision,

audit_no is an autogenerated number

And as to why I will want to filter combo 8 is that not all CCAN's have an active audit_no, at least in theory. But as stated before, I can play with that later.

Daj

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

Post the SQL's of the queries you intend to use for the combobox recordsources . . .

See Ya! . . . . . .

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

 

[DajTwo]

Aceman,

Same as posted before. Because of your last post, now I understand better the concept. I will use the ones already posted earlier.

Combo 8

RowSource (with query): SELECT qry_frm_audit_info.ccan, qry_frm_audit_info.cust_name FROM qry_frm_audit_info ORDER BY qry_frm_audit_info.cust_name;

Combo 10
RowSource (with queries): SELECT qry_frm_audit_sub.audit_no, qry_frm_audit_sub.audit_prepost_date, qry_frm_audit_sub.ccan WHERE [ccan] = [Forms]![audit_info]![Combo8] FROM qry_frm_audit_sub ORDER by audit_no;


If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

I'd like to see the SQL of [blue]qry_frm_audit_info[/blue] and [blue]qry_frm_audit_sub[/blue]

See Ya! . . . . . .

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

 

[DajTwo]

qry_frm_audit_info

SELECT tbl_customer.cust_name, tbl_customer.ccan
FROM tbl_customer INNER JOIN tbl_audit ON tbl_customer.ccan = tbl_audit.ccan
WHERE (((tbl_audit.audit_no) Is Not Null))
GROUP BY tbl_customer.cust_name, tbl_customer.ccan
ORDER BY tbl_customer.cust_name;

qry_frm_audit_sub

SELECT tbl_audit.ccan, tbl_audit.audit_no, tbl_audit.[audit_sub_#], tbl_audit.audit_status, tbl_audit.[audit_#_units], tbl_audit.audit_status_date, tbl_audit.audit_officer, tbl_audit.audit_officer_type, tbl_audit.audit_prepost_date, tbl_audit.audit_exposure, tbl_audit.audit_physical_date, tbl_audit.audit_posted_date, tbl_audit.audit_comments, tbl_audit.audit_closed_date, tbl_audit.audit_closed_by, tbl_audit.audit_closed_time, tbl_audit.[audit_sign-off_date], tbl_audit.[audit_sign-off_by], tbl_audit.audit_guidelines, tbl_audit.audit_extension, tbl_audit.audit_expiry_date, tbl_audit.audit_vendor
FROM tbl_audit
WHERE (((tbl_audit.audit_no) Is Not Null));


If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

Change the name of the combo8 to cbxName and combo10 to cbxAuditNum, then try the following:

[blue]For cbxName:

SELECT ccan, cust_name FROM qry_frm_audit_info;
Column Count = 2
Column Widths = 0";1"

AfterUpdate event should be:
   Me!cbxAuditNum.Requery
   Me.Recordset.FindFirst "[CCAN] = '" & Me!cbxName & "'"



For cbxAuditNum:

SELECT audit_no, audit_prepost_date, ccan WHERE [ccan] = "'" & [Forms]![audit_info]![cbxName] & "'" FROM qry_frm_audit_sub ORDER by audit_no;
Column Count = 3
Column Widths = 1";1";0"

AfterUpdate event should be:

   Dim frm As Form
   
   Set frm = [audit_subform].Form
   frm.Recordset.FindFirst "[audit_no] = " & Me!cbxAuditNum
   Set frm = Nothing[/blue]

See Ya! . . . . . .

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

 

[DajTwo]

Aceman,

I want to thank you for the efforts and the coping you had to do trying to get this double combo working but it is not. I still get the error on the 2nd combo box.

I will have the users remember and select the audit_no instead of going through the select CCAN 1st.

Not as pretty and professional but it will have to do. You have better things to do.

Thanks for all your attention to this problem. I really appreciate and learned from your knowledge and advices.

Thanks

Daj

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

If you change your mind you can always come back. I just know your a hair trigger away (I've done this many many times in many different secnario's).

Also you may wish to sign up for 4Shared (free) where you can upload the DB (or a scaled down model) and provide an assigned link to us for downloading.

See Ya! . . . . . .

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