How to set a combo box default value to the previously selected value

[Declining]

Hello All,

I’d like to make an enquiry as to how to set the default value in a combo box to the previously select value in MS access 2003.

I have 50 or so possible items that can be selected from the combo box and scrolling though them each selection time will be too time consuming for the user. As the majority of the time the user will be selecting the same value repeatable, it makes sense to me to try and set the default value to the previously selected one!?!? Is my logic correct?

When I try to do this I get diverted to accesses expression builder, however I am unable to find the correct expression to do this?

Any advise?

D

[FishVal]

Hi, Declining.

Take a look at Repeating values for a field in several records thread.

[missinglinq]

In the code editor in the AfterUpdate event for your combobox you can use this to set the default (assuming it's a text field)

Private Sub YourComboBox_AfterUpdate()
  YourComboBox.DefaultValue = """" & Me.YourComboBox & """"
End Sub

Welcome to TheScripts!

Linq ;0)>

[Declining]

Thanks for the quick response guys however my problems continue.....

The default value is updated to the previously selected value (i used the ComboBox.DefaultValue = """" & Me.PartNumbers & """" solution) on the first command button click however on subsequent entries nothing is added. It appears i need to reset the event procedure how do i do this?

Thanks,

[FishVal]

Thanks for the quick response guys however my problems continue.....

The default value is updated to the previously selected value (i used the ComboBox.DefaultValue = """" & Me.PartNumbers & """" solution) on the first command button click however on subsequent entries nothing is added. It appears i need to reset the event procedure how do i do this?

Thanks,

Post the whole AfterUpdate event handling sub.

[Declining]

Private Sub PartNumbers_AfterUpdate()

PartNumbers.DefaultValue = """" & Me.PartNumbers & """"

End Sub

[missinglinq]

The DefaultValue should remain the same until you either make a new selection from your combobox or until you close your form! When closing your form, this DefaultValue will be lost, and will only be "reset" when you open the form again and make a selection from the combobox.

what exactly did you mean by the statement

(it worked) on the first command button click however on subsequent entries nothing is added.

Also, FishVal asked you to post all of your code in the AfterUpdate event, but all you show is the setting of the DefaultValue. Where's the code showing what you do with the selected value after the selection?

Linq ;0)>

[Declining]

Hi MissingLinq, FishVal

"It worked"

a default value of the previous value selected was inserted into the combo box. Per MissingLing's discription.

The DefaultValue should remain the same until you either make a new selection from your combobox or until you close your form! When closing your form, this DefaultValue will be lost, and will only be "reset" when you open the form again and make a selection from the combobox.

My new problem however now is it appears once i enter the default value i cannot enter additional information using the command button event below

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
'Add new record
    DoCmd.GoToRecord , , acNewRec
'Run query to update record
    DoCmd.SetWarnings False
    stDocName = "Studentsquery"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.SetWarnings True
'Run update query once predetermined classsize reached
    If (DCount("classsize", "Information", "classsize = False") = 15) Then
    DoCmd.SetWarnings False
    stDocName = "UpdateQuery"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.SetWarnings True
    End If
 
Exit_Command1_Click:
    Exit Sub
 
Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click
 
End Sub

Thanks,
Decline

[FishVal]

Hi, Decline.

It would be a good idea to post [Studentsquery] and [UpdateQuery] as well.

[Declining]

No problem

Update query
Every time a record is saved it adds a No count to classsize. Once the query is run all No, are turned to Yes (i use the No's to count to class size 15, it reset then to yes once complete, and i start again)

UPDATE Information SET [Information].classsize= Yes
WHERE ((([Information].classsize)=No));

Student query
This query inquires in an linked file for a description that matches the Partnumber chosen:

UPDATE Sheet1 INNER JOIN Information ON Sheet1.[Part Number] = [Information].classsize SET [Information].Description = sheet1.description;

[FishVal]

You'll easier find a problem via debugging the code. Read the following tutorial.
WIP: Debugging in VBA

Just for starters.
toggle breakpoints on

• the 1st row
• the rows with DoCmd.OpenQuery ....
• the row with if DCount(......

check first whether code execution flow reaches that points.