Syntax to populate a field in a subform based on another field in the same subform

[wheatlandacctech]

Hello,

What is the syntax for populating a field on a sub form based on another field on the same sub form?

Thanks,

Joel

 

[Eugene-LS]

What is the syntax for populating a field on a sub form based on another field on the same sub form?

In sub form module:

    Me.DestFieldName = Me.AnotherFieldName

 

[The_Doc_Man]

If you are doing this from some OTHER position, you might have more of an issue. I.e. if you are trying to do this from the parent form. So let us know from which form you are attempting to do this. From the sub? Or from the parent?

 

[wheatlandacctech]

I should have been more specific. I have two comboboxes on a subform. Let's call the subform "subDetails" and the two comboboxes named "accountID" and "subaccountID" I want to populate the subsccountID field based on the selection from the AccountID combobox. Both comboboxes are on the subform.

I have the following expression in the criteria line of the underlying query that populates the combobox subaccountID. [Forms]![AccountRegister]![subDetails]![accountID]. What am I doing wrong?

Thanks in advance.

Joel

 

[Eugene-LS]

What am I doing wrong?

Your question is older than the universe!
The point is that by applying selection conditions to the secondary combo box - you do it for all records of sub form.

 

[wheatlandacctech]

If you are doing this from some OTHER position, you might have more of an issue. I.e. if you are trying to do this from the parent form. So let us know from which form you are attempting to do this. From the sub? Or from the parent?

I'm trying to do it from the subform. Both combobox controls are on the subform. I'm not very experienced so I maybe am not explaining this correctly. Sorry for any confustion.

Thanks

Joel

 

[The_Doc_Man]

So are you saying that you want to re-select one sub-form based on the (manual) selection of the other subform? What makes it unclear is this:
From post #1 of this thread:

populating a field on a sub form based on another field on the same sub form

But later in post #4 you said:

I want to populate the subsccountID field based on the selection from the AccountID combobox. Both comboboxes are on the subform.

So now it is not merely a field, it is now two combo boxes, one with a manual selection and the other with an automatic selection. Is this a reciprocal situation where you could have made a choice on the 2nd box to reset the selection on the 1st box? Or is it one-way only?

I have no doubt we can get an answer, but first we need a consistent question. We understand that new members have trouble with questions sometimes, which perhaps is why Eugene responded as he did.

I'm going to take a stab at this based on guessing what you want. Here is a link with some code.

Select item from a ComboBox

I have Access 2010 form which has a ComboBox cmbSubTopic which lists two columns (SubTopicID and SubTopic). The combo box is bound to a field containing SubTopicID. The SubTopicID column in the com...

stackoverflow.com

The idea is that you must find the combo box row that has the value you want and set that row's .Select property to true. You would have to previously do an .Undo on the combo if it already has a selection. You can tell if a combo has a selection because the cbo.ListIndex will be something other than -1 when a selection has occurred. (Note that the combo's list index starts at 0, not at 1.)

You DO NOT jam-cram a value into the box because alters the underlying rows in some cases. You have to FIND the row and then select it.

Maybe that link and my explanation will be enough to help you. If it is not, then you must consider trying to improve your question. But maybe I've provided what you really wanted here. And if I'm close, at least you can see the general principle involved.

 

[CJ_London]

I have two comboboxes on a subform. Let's call the subform "subDetails" and the two comboboxes named "accountID" and "subaccountID" I want to populate the subsccountID field based on the selection from the AccountID combobox.

sounds to me like you are talking about what is referred to as cascading combos. If your subform is continuous or a datasheet you have to take additional steps. See this link and inspect the code

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

 

[wheatlandacctech]

I apologize that I've been unclear with my message. I can see that I did type different things in different messages. I'll try to write more clearly.

Here is the code I have in the AfterUpdate event for accountid.

Me.cboGLAccountID.RowSource = "SELECT accountID, accountname " & _
"From tblChartofAccounts " & _
"Where accountID = " & Nz(Me.cboAccountID) & _
"Order By accountID"

I get an error message when I trigger the afterupdate event that says "missing operator" I must have done something wrong.

Thanks,

Joel

 

[The_Doc_Man]

You would get that error if, in your SQL statement for .RowSource, you had Me.cboAccountID = null - which it would be if nothing is selected in the combo at the time. or if there is no space after whatever is returned from NZ(Me.cboAccountID ) - which there won't be if it is numeric. You are missing a space between that NZ() expression and the Order By clause.

When you run things together, Access gets confused. Let's say for argument's sake that cboAccountID happens to have select the number 1. So let's build that SQL. Let's substitute the 1 for the NZ() function and finish building the string.

.RowSource = "SELECT .... WHERE AccountID = 1Order By AccountID"

SQL has no idea what 1Order is, but it LOOKS like a variable - followed by the word "BY" which, in this case, does NOT appear to be part of the ORDER BY clause, 'cause that got bollixed up by the 1. So now you have "Account = <<some variable>> <<some variable>>" because if BY isn't part of a clause it has to be a variable. And that would be your missing operator.

 

[Gasman]

I ALWAYS put the space required between sql words at the start of each string?
You are missing a space before the ORDER statement.
If you put it all into a sring variable and Debug.Print it or walk though your code, you would see any errors?

Even if you still could not, you could post the output back here for someone else to have a look at it.

 

[CJ_London]

When your sql is corrected I don't see that it is going to return the right recordset - unless you only expect one record? In which case why do you need a combo? It is rather confusing that you appear to have two fields called 'accountID'

If you are expecting more than one record to be returned then they will all have the same ID with different names so unless you are using the name as your bound combo column there is no point in selecting anything as it will always choose the first record.

 

[Gasman]

Now crossposted https://www.accessforums.net/showthread.php?t=85970

 

[The_Doc_Man]

Joel (@wheatlandacctech ) - please note that when you post the same question on different forums (called crossposting) without telling anyone, it is an insult to the folks who have tried to help you. First by implying you have not been helped (even if you have been), but second because you might get answers from us anyway, only for us to find that you already had answers from another source. This wastes our time.

Proper etiquette for a crosspost is you tell us you crossposted and provide a link. That way we don't waste our time if you have an answer from another source. And if your goal was to shop around because you didn't like the answer you got, TELL us why the answer wasn't good for you. We are all volunteers here. We are giving our free time to people like you. Respect that fact, please.

 

[wheatlandacctech]

Joel (@wheatlandacctech ) - please note that when you post the same question on different forums (called crossposting) without telling anyone, it is an insult to the folks who have tried to help you. First by implying you have not been helped (even if you have been), but second because you might get answers from us anyway, only for us to find that you already had answers from another source. This wastes our time.

Proper etiquette for a crosspost is you tell us you crossposted and provide a link. That way we don't waste our time if you have an answer from another source. And if your goal was to shop around because you didn't like the answer you got, TELL us why the answer wasn't good for you. We are all volunteers here. We are giving our free time to people like you. Respect that fact, please.

I am so sorry. I didn't understand exactly which site I was even on. I really appreciate all of your help! You have helped me. I did get it figured out with your help and sighing a prayer for help. I'll remember that for the future. I now understand what cross posting is.

Thanks,

Joel