setting a checkbox in a table

[Jason1]

To start I am using Access 2007.

I have a form that has a combo box on it as well as a control button. When the button is clicked access will open a report, and print the record matching that of the selection in the combo box. That is all well and good. However, I would like it to also open the table in which the record is located, and set (check) a checkbox in that table. How would I go about doing this.

 

[jzwp22]

Assuming that you only want to update the check box for the record that was selected in the combo box, then you would need to run an update query. I would normally build the query in code and then run the query.

Dim mySQL as string

mySQL="Update tablename SET checkboxfieldname=-1"
mySQL = mySQL & " WHERE keyfield=" & me.comboboxname

currentdb.Execute mySQL, dbfailonerror

The code above assumes that the key field is numeric and the the bound field of the combobox corresponds to that key field

 

[Jason1]

Thanks for the reply. Would I put this in the on-click command in VB editor?

I barely grasp the concepts related to VB so I may need a little more help with this.

 

[jzwp22]

Thanks for the reply. Would I put this in the on-click command in VB editor?

Yes

I assume that the command button is on the form with the combo box, so you can use the me.controlname shorthand notation to refer to a control (combo box, list box, textbox etc.) on the form. If the controls were on another form you would have to use the full form reference:
forms!formname.controlname

The tricky part is setting up the mySQL string (i.e. your query text)

BTW, you can add the following statement just before the execute statement

debug.print mySQL

The statement will display the query text in the VBA immediate window, so that you can verify that it has the proper syntax. You can also copy and paste the SQL text from the immediate window to a new query in Access and test the actual query. This will help in troubleshooting. Here is the code example with the placement of the debug.print statement

Dim mySQL as string

mySQL="Update tablename SET checkboxfieldname=-1"
mySQL = mySQL & " WHERE keyfield=" & me.comboboxname

debug.print mySQL

currentdb.Execute mySQL, dbfailonerror

 

[Jason1]

Please forgive my ignorance, but if I copy and paste the code you have here in the on-click section of the code and change the table name, checkbox field name, and combobox name it should work?

Do I have to change the WHERE keyfield portion? and if so, what do I put there?

Thanks

 

[jzwp22]

Yes, you will have to change the key field to your key field name. Since you said that you have are printing a report based on a specific item selected in the combo box, I am guessing that the bound field of the combo box contains data that identifies the record. The update query needs that value as well so that it updates the correct record, so we need the field name in the table that corresponds to the bound column of the combo box.

Can you post the fieldnames in the table that the update query is going to update as well as the row source of the combo box?

 

[Jason1]

The update query is going to update the field named "InProcess" in a table named tblWorkOrder. The row source for the combobox is name "WorkOrderNum" in the same table.

 

[jzwp22]

The row source for the combobox is name "WorkOrderNum" in the same table.

The row source normally resembles a query and begins with SELECT....FROM...

Can you post the text of the row source and confirm whether the bound column property shows a 1?

In tblWorkOrder, what is the name of the field that corresponds to the work order number (is it WorkOrderNum?)? Does that field contain unique values?

 

[Jason1]

Sorry about that. I misunderstood what you were asking for.

Here it is:

SELECT [QryIncompleteWO].[WorkOrderNum], [QryIncompleteWO].[Sku], [QryIncompleteWO].[ShipDate] FROM QryIncompleteWO;

 

[jzwp22]

The bound column of the combo box looks to be workordernum, so I assume that is also the key field in the table you are updating, so here is the code. Is the workordernum field in table a numeric datatype?

Dim mySQL as string

mySQL="Update tablename SET checkboxfieldname=-1"
mySQL = mySQL & " WHERE [COLOR="Red"]workordernum[/COLOR]=" & me.comboboxname

debug.print mySQL

currentdb.Execute mySQL, dbfailonerror

 

[Jason1]

My friend you are a genius. One last question. Previously I had this running off a macro to get the record out of the report as I stated.

If I rewrite that macro, convert it to VB, and then place the code you have given me, it will run both items correct?

Thank you so much for your help

 

[jzwp22]

Yes, you can run it all together in VBA, the macro is probably just a docmd statement with a where clause. I believe there is a utility in Access to covert a macro to VBA. Once converted, you should be able to copy the code that opens the report to code we have been working on.

 

[Jason1]

Hello again,

I am working on this exact situation again, except, I am using text fields as opposed to numeric fields. I thought I could use the same code, but it is not working. I am assuming it has to do with the fact that the fields are text fields, and not number fields. Can you please tell me what I would have to change to get the above code to work with a text field?

Thanks,

 

[jzwp22]

Text values have to be delimited by single quotes. Numeric values do not to be delimited. Date values need to be delimited by # signs

So if we were to use a text field, the code shown previously would be changed as follows (see single quotes in red):

Dim mySQL as string

mySQL="Update tablename SET checkboxfieldname=-1"
mySQL = mySQL & " WHERE sometextfield='" & me.textboxname & "'"


If you were dealing with a date field

Dim mySQL as string

mySQL="Update tablename SET checkboxfieldname=-1"
mySQL = mySQL & " WHERE somedatefield=#" & me.textboxname & "#"

 

[Jason1]

thanks again,

I knew it would be something simple like that, but I had no idea where to look to figure it out other than here. Is there a website that has examples of stuff like that, that is easy to access?

 

[jzwp22]

I don't know of a formal website; I actually bought a book several years ago on VBA for Access. The single quotes for text fields and # signs for dates are the only delimiters I have ever worked with.