Setting an unbound combo box value

Topic

New Reply

I have a slightly different twist to the question I had posted last week about using SetValue in order to set a combo box on a different form to match the name on the form. The procedure I had described last week (subscriberID.DefaultValue = Forms!Subscribers!SubscriberID) works ONLY if the combo box is bound to SubscriberID.

This one, I need to set an UNBOUND combo box to match the name that I select in another form.

Reply | Quote

Replies

I am assuming that you are selecting the name on the second form from a combo box. The code should look like this:

Private Sub Combo0_Change()
Form_Form1.Combo0.Value = Form_Form2.Combo0.Value
End Sub

Reply | Quote

Depending on which form you’re setting the value from, here’s a more standard syntax.

From form1:

  forms!form2!Combo0 = Me!Combo0

From elsewhere (but not form2):

  forms!form2!Combo0 = forms!form1!Combo0

It doesn’t hurt to include the Value property, but since it’s the default for editable Access controls, it isn’t necessary.

Understand, though, that this assumes that the comboboxes on both forms have the same values in the first column.

Reply | Quote

Thanks to you both for your suggestions. I had finally figured out why it wasn’t working (I’m still relatively new to VBA).

I had attempted to open a dialog box whenever the item isn’t in the list in the main form. I belatedly realized that if the item wasn’t in the list the first time around, then I wouldn’t be able to set the dialog box combo box to match the main form (no key).

If you have an idea of how I could somehow parse the name in the main form’s combo box into the dialog box, that’d be great.

Again, thanks in advance for your help.

Reply | Quote

The value of the combobox is passed to the notinlist event as the NewData argument.

Reply | Quote

Paul, thanks for the tip. Now, I’m not sure how do I write a code that pastes NewData into the unbound combo box on the dialog box?

I’ve tried Mid statement (can see the NewData content) but how do I tell it to actually insert it into the combo box?

Reply | Quote

This should work:

Forms!MyFormName!MyComboboxControlName = NewData

Reply | Quote

We are getting there. When I set the breakpoint in the VB editor, and I see it says: “cboName=Traub, Carolyn” but the dialog box still shows the Name combo box as blank. Am I missing something?

This is what I did: Forms!fldgSetAppStatus!cboName=NewData and this is the last line in the code. Does the order of code affect this somehow?

Reply | Quote

Could you post the entire procedure?

Reply | Quote

I can’t seem to attach it…

Here’s the code:

Private Sub cboName_NotInList(NewData As String, Response As Integer)
‘ If not listed, then open fldgSetAppStatus

Dim strTitle As String
Dim intMsgDialog As Integer
Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg As String
Dim strEntry As String
Dim strFormName As String
Dim frm As Form
Dim intReturn As Integer
Dim strName As String

strFormName = “frmAcceptance”
strEntry = “ParticipantID”
Set frm = Forms(strFormName)

‘Display a message box asking if the user wants to add
‘a new entry
strTitle = strEntry & ” Not in List”
intMsgDialog = vbOK + vbExclamation + vbDefaultButton1
strMsg1 = “You need to set ”
strMsg2 = “‘s App Status.”
strMsg = strMsg1 + NewData + strMsg2
intReturn = MsgBox(strMsg, intMsgDialog, strTitle)

If intReturn = vbCancel Then
Response = acDataErrContinue
frm.Undo
Exit Sub
ElseIf intReturn = vbOK Then
‘Open form for adding new client
frm.Undo
Response = acDataErrContinue
DoCmd.OpenForm “fldgSetAppStatus”
Forms!fldgSetAppStatus!cboName = NewData
End If

End Sub

Reply | Quote

Why are you using a combobox on the second form? I looked at an example I did and I used the OpenArgs argument of the OpenForm method to pass the string in the NewData variable to the second form. Then in the Open event of the second form I set the value of the control to the OpenArgs property of the form (which should be the same as the NewData string).

DoCmd.OpenForm “fdlgAddRCode”, , , , , acDialog, NewData

Me!txtCode = UCase(Me.OpenArgs)

Reply | Quote

The reason there is a combo box on the second form is because I was trying to filter my main form combo box to show only participants who were accepted to camp. For reasons too lengthy to list here, I couldn’t use conventional filtering so I had to set the combo box to list participants based on their Application Status (Waiting List, Accepted and Not Accepted). If the person is still on Waiting List, then that person wouldn’t show up on the combo box. This is where my code fires and opens second form which enables me to look up that person and change the status and then requeries the main form so that the person would show up there.

1. Does the code you just posted work with combo boxes?
2. I don’t understand your code. Which parts of it that I need to switch with names of my forms/fields?

Thanks for your time and patience! I’m learning a lot from this.

Reply | Quote

Sorry for the unexplanation but it’s been one of those days. See below for details:

‘bold text represents objects you will need to rename to your own object names.

‘This will open a form in dialog mode passing the value from form 1 to form 2 using the OpenArgs argument.
DoCmd.OpenForm “fdlgAddRCode“, , , , , acDialog, NewData

‘In the On Open event of form 2 set the control’s value to the form’s OpenArgs property and set the text in upper case (remove the UCase function if you don’t need that part).
Me!txtCode = UCase(Me.OpenArgs)

I’m not sure if this will work with a combobox or not I don’t have time to test it out just now. Perhaps tomorrow.

Reply | Quote

I’ll test the code, you have done a lot by giving me suggestions, I don’t want to impose on you too much. Smile.

Thanks for the clarification, I tried your code, at the break points, I can see that the name of participant is being passed on but still refuse to show up in the dialog box. I’m gonna look up in my reference book because I recall seeing a similiar situation that worked before…

Reply | Quote

Are there any other columns in the combobox on the second form besides name? And the name is showing up in th on open event of the second form? Strange….

Reply | Quote

The columns in the Unbound Combo Box are:

ParticipantID
Name: LastName&”, “&FirstName

The name is being passed on to the combo box via VBA (I can verify it by “hovering” my cursor and getting a tool tip telling me the results) but it is NOT showing up in the combo box field. I am sorry for not being clear in my previous post.

Reply | Quote

Try passing the ID instead of the name and see what happens.

Reply | Quote

How can I pass the ID if it isn’t in the list?

Guess it is simply not possible. I’ll have to bite the bullet and force users to type the name again in secondary form.

Reply | Quote

It isn’t in the first combo list? Can you add it and set it’s column width to 0″? Then you would have to refer to the column property of the combobox such as:

Me.Name.Column(0)

This being the first column in the list (zero based).

Reply | Quote

Ahhh…Please forgive me for being such a thick-head grin.

So, in the NotInList event, after DoCmd.OpenForm…line, I add this line: Me.Name.Column(0)?

Brent

Reply | Quote

No, in the openform line instead of passing newdata in the openargs argument you would pass the id:

DoCmd.OpenForm “FormName”,,,,Me.cboName.Column(0)

If the Id is the first column. Also, not sure about the commas in that line. Just replace the NewData with that last bit and modify the column number if needed.

Reply | Quote

Got an error message saying that use of null value is not allowed. Oh well.

Thanks for your help, I guess it is simply not possible. I’ll have the user enter the name twice (once in main form and once in the fldg).

Reply | Quote

Well, that’s the thing. It does work cause I tested it when I first started to respond to this thread. There’s just something we’re missing. Would you be willing to post the row source’s of the two boxes?

Reply | Quote

Sure.

This is from frmAcceptance – Name combo box.

SELECT [tblParticipant].[ParticipantID], [LastName] & “, ” & [FirstName] AS Expr1, [tblAppStatus].[AppStatus] FROM tblParticipant INNER JOIN tblAppStatus ON [tblParticipant].[ParticipantID]=[tblAppStatus].[ParticipantID] WHERE ((([tblAppStatus].[AppStatus])=2));

This is the row source for the frmSetAppStatus Name combo box:

SELECT [tblParticipant].[ParticipantID], [LastName] & “, ” & [FirstName] AS Expr1, [tblParticipant].[LastName] FROM tblParticipant ORDER BY [tblParticipant].[LastName];

Reply | Quote

Nothing jumps out at me except why do you have the applicant’s status in a separate table? I’m not saying that this is causing the trouble but it seems like the applicant’s status is an attribute of the applicant and should be in the same table as tha other applicant data. I must be missing something….

Reply | Quote

Ahhh…Welcome to the intrigue of my database world here at Lighthouse. Smile.

I had originally put the application status in the applicant’s table but problem is that not all applicants actually applied for the camp since the applicants comes from mailing list. So, I moved it to Camper table and Volunteer table, had problems with using Union Query in order to combine three different tables (Campers, Volunteers and Easy Volunteers, don’t ask what it means…grin). I simply found it much easier to set up a separate table that held application status. It’s working real well.

Reply | Quote