VBA input from a combo box

Question

This is my first database development project and I'm a little lost.

I have a form with a combo box that's hooked into a unique Lot/Heat Number. When I type a lot number into my Combo Box it pulls up the LotNumber from my records correctly.

How do I now operate on this record in VBA? I need to be able to reference all fields of the record as well as all fields of the previous record to do a calculation. I can program but I simply don't know the syntax.

Thank you in advance!

Johnny Bones Johnny Bones · Accepted Answer · 2013-11-15T20:09:36

I'm not really clear on what you mean by "previous record". To access all records based on the selected LotNumber, you would do something like this:

Dim db as Database
Dim rec as Recordset

Set db = CurrentDB
Set rec = db.OpenRecordset ("Select * from MyTable where LotNumber = '" & Me.ComboBox.Value & "'")

rec now holds a dataset of all records where LotNumber is whatever is selected in your combo. Depending on how you want to select the "previous record", you can always set up another recordset like:

Set rec2 = db.OpenRecordset ("Select * from MyTable where Whatever = '" & SomeValue & "'")

If you're basing it off LotNumber minus 1, it would be something like:

Set rec2 = db.OpenRecordset ("Select * from MyTable where LotNumber = '" & (Me.ComboBox.Value) - 1 & "'")

Then you reference them by saying:

MyVariable1 = rec("WhateverFieldName")
MyVariable2 = rec("SomeOtherFieldName")
MyVariable3 = rec2("WhateverFieldName")
MyVariable4 = rec2("SomeOtherFieldName")

It should be noted that if your LotNumber is an Integer data type, you need to remove the single quotes in the WHERE clause.

VBA input from a combo box

1 Answers