combo box value query

[Vespertin]

Hello

Is it possible to create a combo for a datasheet form that displays a specific value based on the value of the last record.

For example - instead of having the combo display the values 01, 02, 03, 04, 05

the combo will only display 02, if the last record was the value 01.

likewise if the last record was 03, on the next record the value in the combo will only display 04.

The reason I ask is that at present my combo displays 01, 02, 03 etc...and the user can quite easily select the any value they like. Whereas, if it only gives them one value to choose from based on value chosen from the last record - then less mistakes will be made.


Any insight how to achieve this would be very encouraging.

Using A2007

 

[vbaInet]

If you want to force to only select one value, why not auto complete that value for the user and disable the control?

 

[missinglinq]

A Combobox is designed to allow the user to select a value from it; if you do not want your user to be able to make a choice, why use a Combobox? Simply have a Textbox whose Default Value is the Previous Record's Value + 1.

If this actually is a Number, the code would be like this

Private Sub ID_Number_AfterUpdate()
 Me.ID_Number.DefaultValue = Me.ID_Number + 1
End Sub

Linq ;0)>

 

[Vespertin]

Thanks for the response -

combo is not the way forward -

actually the default value is text...so a text field is more appropriate to use.

So on a datasheet form I have a record where text field [status] is "On Loan" when the user starts a new record the default value of [status] will then be "Returned".

Then if the previous record was "returned" - the default value for the next record will be "Item Calibrated"

Thinking of using a select statement like...

Private Sub status_AfterUpdate()

Select Case Me.status

Case "On Loan"

Me.Status.DefaultValue = "Returned"

Case "Returned"

Me.Status.DefaultValue = "Item Calibrated"

End Select

End Sub

However getting problems getting the code to recognise IF on the previous record [status] was "On Loan" then the default value of [status] for this current record is "Returned" - etc..

Could you advise where I would insert this bit of code.

 

[vbaInet]

You can look into using the DLookup() function which will call a value on a query that sorts the records in Descending order by the auto number ID.