Control Source in subform

[alfred67]

Hello

Need help with Control Source in a combobox in my subform.

The main form has a combobox the user picks an Item No from the combobox then it updates the subform with the matching records. The user can now update or add records to the subform. One of the combo box in the subform has a pull down menu and the user can pick the related account for the Account Table

This works great but some"item no" get assign an account automaticaly for example any item No =100 to 200 get account "road" and item no 200 to 300 gets account "sewer" anything else has to pick from the account table list.

I'm not sure how to do this.

Any help will be greatfully

alfred67

 

[alfred67]

Basicly what I'm asking is can I use an IIF statement in my control source based on the combobox in the main form??

 

[John Big Booty]

This sounds like a variation on the theme of Cascading Combo boxes. Where the combo on the sub form is cascading from a combo on the main form.

A couple of questions;

Is the sub-form in continuous view mode?
Will the user be making more than one entry on the sub-form?
and if so will they all be related to the same value in the combo on the main form?​

 

[alfred67]

Hello

I attached a zip file which has Screen shots. I hope it will make it easier to understand what I'm trying to do.

Looking at the screen shots the user must pick which account applies but any item numbered over 100 but under 200 the user must pick "sec 100 Sanitary" same applies with items in the 200 range the user must pick "sec 200 Watermain" but any other item number the user can pick which account applies.

I'm not sure how to set the control on the account combobox to achive this

Thank you

alfred67

 

[John Big Booty]

As I said previously this would seem to be a variation on the theme of cascading combo boxes.

Now you you implement that will largely depend on the answers to the questions I asked;

...

A couple of questions;

Is the sub-form in continuous view mode?
Will the user be making more than one entry on the sub-form?
and if so will they all be related to the same value in the combo on the main form?​

You've answered the first (in your image) but no the second two.

 

[alfred67]

"Will the user be making more than one entry on the sub-form?"--yes
"and if so will they all be related to the same value in the combo on the main form"--yes

 

[John Big Booty]

OK then if all the entries on the sub-form are related to a single value in the combo in the main form you can use a cascading combo box set (also search this form on the subject).

You will just need to put the following code in the main form's On Current event and the On Change event of your primary Combo;

Me!SubformName.Form!ComboName.Requery

This code will ensure that the two combo boxes remain synchronised.

Once a selection has been made in the sub-form combo, you will want to consider locking the primary combo, so that it can not be changed, thus invalidating any selections already made in the combo on the sub-form, and then unlock it again when a new record is started.

Book mark this page for future reference for the correct syntax for referring to Sub-form's their controls and properties.

 

[alfred67]

Thank you for responding.
vba code is not my strength. Can you give me an example on how to do this ??

My thinking is something like this (combo4 is the name of the combobox in the main form Accountcombo is the name of the combobox in the subform)

Select Case cbocombo4.Value
Case 200 to 299
cboAccountcombo.RowSource = "SEC 200 WATERWORKS"
etc
End Select

but I'm not sure where to put the code in Afterupdate (combo4)
Will this work??

Thank you

Alfred67

 

[John Big Booty]

OK here's a quick sample. Yes, it's not accounting based but the principal will be exactly the same.

There are three pieces of code that make this form work;

1. The main form's On Current event
2. The State combo On Change Event
3. and the After Update event on the Suburb Combo

The first checks if the sub-form holds any data and Locks and Disables the State combo if there has been data entered in the Sub-form, and also requeries the suburbs combo to keep it synchronised with the state combo

The second simply requeries the suburbs combo to keep it synchronised with the state combo.

And the final piece, saves the record in the main form and then Locks and Disables the State combo once a choice has been made from the suburbs combo

Open the forms in design view and click on the Events tab to see the various pieces of code, all very simple and straight forward.

 

[alfred67]

Hello

I just can't get this to work. I'm thinking I should be using the Case funtion and my account box should be a text box. Help!!

Please look at my screen shot should explain what I'm trying to do.


Alfred67

 

[John Big Booty]

Firstly have you had a play with the sample DB I posted, and pulled it apart to see what makes it tick?

Now your Item Number combo box is the equivalent of the State Combo Box in my sample, and the Account Combo is the equivalent of the Locality Combo in my sample, so you need a query that is similar to the Query QRY_CasCombo in my sample, you also need similar code to the three pieces I mentioned earlier.

 

[alfred67]

Hello

I will relook it.

Thanks
Alfred67

 

[alfred67]

I got it to work with this code: (placed on Change Combo4)

' Get the first digit of the value in the combo box

Dim firstNumber As String

firstNumber = Left(Combo4.Text, 1)

' Set the account number combo box

On Error Resume Next

[Form_Item Payment Subform].Combo21.Value = CInt(firstNumber)

and it works but I found when I tried to add another record (click on date it doesnot fill the account box)

How do I re-wite above code in my subform so when I click on date it updates the account box. Combo4 is in main form "Item Payment Record"

Alfred67

 

[John Big Booty]

Book mark this link for future reference. It has the correct syntax for referring to subforms and their controls.

However I'm still not sure you've really looked at the sample and understood how it works, as if you had you'd not be going down this path

You would see that I am using the Query QRY_CasCombo to populate the second combo box.