Updating Combo Box query/table

[vanessa03]

I am not quite sure the best way to do this. I have a combo box that uses a query on table tblQCMillMatCstType. I then display in a locked text box on after update of combo the following:
txtCurrentRunNo.Value = cboMatCstType.Column(3) (which is CurrentRunNo in that table). I now want to have a command button that when you click it, it will take the cboMatCstType.column(3) and add 1 and update the table with next run number. I would appreciate any help on this. Thanks.

 

[vbajock]

At the top of your code module, you need to create a variable with a Dim statement so that it can be used anywhere you call it in your form events. I am assuming it is an integer:

Dim lNextRunNo as Integer

Now in your after update event, you can assign the value of the combo box to the variable:

Sub SomeSub_AfterUpdate()
lNextRunNo=cboMatCstType.Column(3)

txtCurrentRunNo.Value = lNextRunNo

End Sub

Now in your command button on click event you would put:

Sub SomeCommandButton_Click()

lNextRunNo=lNextRunNo+1
'code to update the table goes here
End Sub

I don't have enough info to tell you how to update the table. Is it a different field in the same table?

 

[vanessa03]

Thanks so much for replying, but I guess what I am asking do I use a SQL update command to update that record or do I have to actually open & close a recordset and update it that way? I was just afraid since the record is open via the combo box query it would be locked and not let me update it. Hope I am making sense.

 

[TheAceMan1]

How are ya vanessa03 . . .

I'd go with SQL. However not sure if it should be Append or Update.
[ol][li]Is [blue]txtCurrentRunNo[/blue] a primarykey? ... typically primarykeys are assigned before a record is saved. You want to avoid orphan keys here (records with only primarykey data).[/li]
[li]The [blue]txt[/blue] in txtCurrentRunNo signifies its a text datatype. This means you want to increment a numeric string value. Conversion to numeric and back to text may be necessary.[/li]
[li]Is the combobox [blue]bound[/blue] or [blue]unbound[/blue]?[/li]
[li]Does the combo reside in the header, footer or detail section?[/li][/ol]
Some assemblence of your tables (tablenames, fieldnames, datatypes) or the table in question is in order.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[vanessa03]

Thanks AceMan for getting back to me. I did make an Update Query to add one to the CurrentRunNo (integer in table, but displayed in text box txtCurrentRunNo on form). Then in code I used the following in the click event of my button: DoCmd.OpenQuery "qryQCUpdateNextRunNo" (this update query used the combo box as selection criteria) and it worked just fine. Thanks again all your invaluable help you have given to people over the years. Having no VBA training, this forum has been my training ground due to great people such as you and Vbajock, etc.