Solved Default value of combo box on one form based on combo box value in another (1 Viewer)

GoodyGoody

Registered User.
Local time
Today, 22:58
Joined
Aug 31, 2019
Messages
120
Hi All,

It's been a while. Lock down definitely got in the way as all races were cancelled. now there is a prospect of them restarting I have the impetus to restart my race database project. Anyways, I feel i should know the answer to this and that it really shouldn't be as complicated as I seem to be making it as I have done some pretty cool stuff with race timing including series' results and teams and team series results. Right so,

I have a form frmSelectRaceEvent where I can select an existing raceevent (Racename plus date of race) to Edit or I can select 'Add New Race Event'. If I select 'Add New Race Event' AND I have selected a RaceName (none of the existing race events are the correct one) I just want to pass the already selected racename to the new 'Add New Race Event form just to save the user from selecting the Race name again. I'm passing the cmbRaceName value through Openargs to the 'Add Race Event form no problem but I can't fire the cmbRacename combo on the new form with the pass Race ID. The code is below, what am I doing wrong please?

In the Open Event of the 'Add Race Event' form I have

?
If Not IsNull(Me.OpenArgs) Then
Me.cmbRaceName.RowSource = "select Racename.racename from Racename where [racename.ID] = " & me.OpenArgs
Me.cmbRaceName.Requery
Me.txtRaceDate.SetFocus
End If
?

It runs the code fine but no race names at all appear in the combo box (note I still want the user to be able to select another race name but just position the combo at the one they chose on the last form as a good guess that that is the one they want to add.

As ever help is always hugely appreciated.

ThanksGoodyGoody
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
Check what is in OpenArgs.
Even if anything is in it, all you are going to select is that ID ?

You need to select all the IDs, then set the combo to your ID.

No need for a requery either. If you change the Rowsource
 

GoodyGoody

Registered User.
Local time
Today, 22:58
Joined
Aug 31, 2019
Messages
120
Openargs contains the correct id. I just cannot get the combo on the destination form to who all records but default to the one I want. Could you send some sample code? RaceName is the table, Racename.ID is the bound column and Racename.Racename is the column I want to show.

Thanks
Stephen
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
That is because that is the only one you are bringing in because of your Select statement.? I've already told you that.?

You need to bring in them all, and then just set the combobox to the value in your OpenArgs.?
 

GoodyGoody

Registered User.
Local time
Today, 22:58
Joined
Aug 31, 2019
Messages
120
Yep I don’t know what you mean by being in them all and set the value of the combo box. Can you give me some sample code or at least the command steps please? Thanks.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
You do not specify any criteria, you want all the records for the combo. THEN you set the combo to the value of your OpenArgs.

So leave the rowsource alone and just set the combo.

Try it out, it should only take you a few minutes.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:58
Joined
Feb 19, 2013
Messages
16,553
not sure if it has an impact but not a good idea to have a field name the same as the table name

And what have you got for the number of columns and column width properties?
 

GoodyGoody

Registered User.
Local time
Today, 22:58
Joined
Aug 31, 2019
Messages
120
CJ, you’re right on the names. 2 columns, bound column is 1 widths 0 cm; 5cm. The drop down works fine. I just want to give the combo box a default value and just don’t know how to do that. I’ve tried cmbracename = me.openargs but access just tells me I can’t assign a value to the object

gasman, yep that’s my issue how else do I assign a value to the combo if not the above which is giving me an error. I’m sure it’s really easy I have no problem with the logic just not something I have done in access so just to know

thanks
S
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
Sounds like the combc is bound then.?

I think you will need to scroll through the combo source and identify which row matches to the ID and set that to selected. I was assuming the combo was not bound. However I have just tried on another of my DB where a combo is bound and it works fine.? :unsure:

I cannot find a link for searching combo, but I think it should be the same as a listbox?

Code:
Private Sub txtSearch_AfterUpdate()
Dim i As Long
If Len(Me.txtSearch & "") = 0 Then
    Exit Sub
End If
With Me.lstCrew
    For i = 0 To .ListCount - 1
        .Selected(i) = False
        If Left(.Column(1, i), Len(Me.txtSearch)) = Me.txtSearch Then
            .SetFocus
            .Selected(i) = True
            .ListIndex = i
            Exit For
        End If
    Next
End With

End Sub

I would be using Me.Me.cmbRaceName as well.?

This worked for me?

Code:
  If Me.OpenArgs <> "" Then
        Me.CaseWorker = Me.OpenArgs
    End If
and it quite happliy overwote the value for that control for the record?
HTH
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 28, 2001
Messages
27,001
To get sample code for selecting something on the form based on a combo box, build a combo box with Access wizards enabled. When you get around to defining what to do with the combo, one of the options is "show a particular record based on my choice." (Or words to that effect.) When you do that, you will see code appear in the form's Class module based on (I believe) a _Click event. This will show you how to select a given record on the form.

Gasman has been telling you that you don't want to select anything in the way you were attempting. I'm with him on this one. Instead of filtering the form down to a single record, define the .RecordSource of the form to allow the entire table. Then have that combo box on the form that was built using the "show a record" option I described above. AND once you have that code to move to the given record, you have a template for the third thing, which is what you actually asked about. In the form's _Load event, you can put code that looks at the .OpenArgs and if there is something there, use similar code to what the combo wizard built for you to select the record identified in the .OpenArgs value.
 

GoodyGoody

Registered User.
Local time
Today, 22:58
Joined
Aug 31, 2019
Messages
120
Thanks, that gives me something to work with Gasman and thanks Doc_Man for the tip on sample code. The combo box is currently bound to the whole table. I was just trying out different things hence rowsource but that clearly wasn't working. :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
@The_Doc_Man
The O/P was adding a new record, so just wishes to populate the combo?
That was all I was aiming for.?, getting the relevant row of the combo to show.?

Perhaps it could be done by setting the default value as well?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 28, 2001
Messages
27,001
The part I was addressing was to select based on the .OpenArgs value. I just proposed a roundabout way to get there.
 

GoodyGoody

Registered User.
Local time
Today, 22:58
Joined
Aug 31, 2019
Messages
120
We got there in the end! Thanks. Turns out it is stupidly simple. Just use the Default value and hey presto perfect! Thanks for the help guys as ever
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
We got there in the end! Thanks. Turns out it is stupidly simple. Just use the Default value and hey presto perfect! Thanks for the help guys as ever
Might want to mark it as Solved then. Button on the top right of the post?
 

Users who are viewing this thread

Top Bottom