Pre-position a combo box

[Bill]

How does one pre-position a select in
a combo box?

I've just added a new record to the Rowsource
and have Required the combo and want to have
the newly added record pre-selected following
the Requery.

I would necessarily have to find the item based
on its display name, as I don't yet have its newly
assigned autonumber.

Thanks,
Bill

 

[Guest]

Hello Bill,

Take a look at the control's default value property in the help file. That
should help.

Regards,

Paul

 

[Bill]

Default Value won't help me if I don't know the
index of the newly added record.....or am I
missing something here?
Bill

 

[Guest]

Hello Bill,

You'll have to query the table by the new record's name. Try this in the
Northwind sample db:


Private Sub Command0_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQl As String

strSQl = "SELECT CustomerID, CompanyName " & _
"FROM Customers " & _
"WHERE CompanyName = ' " & Me.Text1 & " ';"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQl, dbOpenSnapshot)

With rs
rs.MoveFirst

' Prints to immediate window to debug.
Debug.Print .Fields(0) & vbTab & _
.Fields(1) & vbTab

Me.Combo3.DefaultValue = " ' " & .Fields(0) & " ' "

.MoveNext

End With

Set db = Nothing
Set rs = Nothing

End Sub


Hope this helps.

Regards,

Paul

 

[Guest]

Sorry Bill, but you have to remove the additional space between the first
single quote and the first double quote and the second single quote and the
second double quote in the SQL statement. I put them in my response to make
it easier to identify them. Thanks.

 

[Bill]

Hi Paul,
Would you believe that I was about to post that the
only way I could see to utilize the DefaultValue
property would be to first open a DAO Recordset
and locate the newly added record to determine
it's index position. BUT, that I was having a hard
time believing that it required all that effort for what
would seem like a small matter......OH WELL!
Thanks,
Bill

 

[Bill]

Hummm! Not in my 2003 Northwind. What version are you using?
Bill

 

[Bill]

Paul,
As I look at the Northwind code, I'm hard pressed to answer
why one simply wouldn't set the DefaultValue directly given
that "text1" is already known. I've got to be missing something
here.

I created a form in the 2003 Northwind.mdb with a combo object
named combo3. And, copied the code from your post with
syntax corrected into the class module and ran the code. The
results were as expected, namely, that the code had in fact
found the company that I'd set in code for purposes of testing,
i.e., :
Dim text1 as string
text1 = one of the company names

Bill

 

[Bill]

Well Paul, in spite of my intuitive thoughts to the
contrary, I adapted the exact code you posted
(adjusted, of course, for my stuff) and it worked
perfectly.
Thanks,
Bill

 

[Guest]

Sorry for the mixup Bill. Just in case, the 'Text1' is actually a text box
control on the form along with a combo box and a command button. I set the
value for the text box to a name in the Customers table. I then could call
it's value property by using 'Me.Text1' to get the name and pass it to my SQL
string. I'm glad it worked.

Regards,

Paul

 

[Guest]

Yep, sometimes there are things that seem so trivial and easy to the user
where it takes them a second or two to complete may actually take the
programmer a hundred (or more) lines of code just to accomplish the task and
ease usability concerns. I'm finding that having a library of snippets of
code of various tasks can make things a whole lot easier. Thanks.