Solved Default value of combo box on one form based on combo box value in another

[GoodyGoody]

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]

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]

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]

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]

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]

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]

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]

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]

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.?

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

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?

  If Me.OpenArgs <> "" Then
        Me.CaseWorker = Me.OpenArgs
    End If

and it quite happliy overwote the value for that control for the record?
HTH

 

[The_Doc_Man]

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]

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]

@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]

The part I was addressing was to select based on the .OpenArgs value. I just proposed a roundabout way to get there.

 

[GoodyGoody]

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]

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?