Setting a yes/no to true from a pop up

[Bill Fischer via AccessMonster.com]

I have a record review subform in datasheet view which is based on a non
updatable query. Is there a way to click on the record on a certain field
(I'm hoping for the yes/no field but willing to use any field on the record)
to update the underlying table. The source query is highly filtered making
it nonupdatable and hence I'm trying to use the pop up to change the data.
I'm stuck with the datasheet view (my manager wants to be able to look at all
records to compare. The popup is easy, having a message to user (Do you want
to update this record?) with Yes and No command buttons. What I think I
need is code to transfer record ID to the form and then with updated to table.
Is any of this possible? Any help would be greatly appreicated.

Thanks,

Bill

 

[Guest]

You could use an Update query or SQL to do this. The SQL would be the
easiest. In a datasheet view, the control (textbox) name will return the
value of the current row, so If you need to update the underlying table:

Not guaranteed code, will need some tweeking.

Dim strSQL as String

strSQL = "UPDATE MyTable SET MyTable.SomeField = & '" Me.txtBox _
& "' WHERE MyTable.RecordId = " & Me.txtRecordID;"
CurrentDb.Execoute(strSQL), dbFailOnError

 

[Bill Fischer via AccessMonster.com]

Thanks for the reply Klatuu,

Couple of questions:

Is there any to pass these to a pop up form to give the user a chance to back
out? I currently have a pop up set up when a one certain field is clicked,
but lack the know how to transfer the ID to the pop up and then to the table.
The pop up with command buttons gives the user the chance to move on or
decline, I would like to use this incase fo inadvertent click or even double
click.

Being I need to update a yes/no field, would I need to include in the string
a "= true"?

Playing around with the string, I get a Expected End of Statement error at
the Me.txtBox.

Showing my enthusiasim to learn SQL just not my knowledge.

Thanks for any help,

Bill

 

[Guest]

I believe there is a syntax error in my originla post (note I said it was
untested)
Try this:
strSQL = "UPDATE MyTable SET MyTable.SomeField = & '" & Me.txtBox _
& "' WHERE MyTable.RecordId = " & Me.txtRecordID;"

You could use the OpenArgs argument of the OpenForm method to pass the ID to
the popup or if the form that opens the pop up is the only form that uses the
popup, you can reference it directly by referring to the opening form:

=Forms![MyOpeningForm]![txtRecordID]

I'm not sure I understand where you are setting the Yes/No. If it is in the
pop up when you update the record, you could do it in the SQL above. And, if
you have passed the values to the popup using the OpenARgs, you could
reference those values to update your table or, using the example above,
reference the form that opened the Popup.

Sorry if this is not too specific, but I guess I don't have a clear picture
of what all is going on.

 

[Bill Fischer via AccessMonster.com]

Sorry for the confusion. The yes/no is in the table to be updated, I thought
a setting check box to "true" statement would be necessary. I do show a
checkbox in the the datasheet form with every record just for user reference.
Even though it is not an updatable field being the behind query is based on
two Totals queries in a union query, I was looking for the user to be able to
click the checkbox which would open the pop up form (if not I can use another
field with a double click). The pop up has two command buttons one for yes
and another one for no, if the user would like to update the record. No
closes the pop up and returns to orginal form. The Yes button on a click is
what I would like to handle the update to the underlying table and then
requery.

The click on the form's checkbox may not be possible unless I can get around
the Access user error of trying to update a nonupdatable query.

Thanks,

Bill

I believe there is a syntax error in my originla post (note I said it was
untested)
Try this:
strSQL = "UPDATE MyTable SET MyTable.SomeField = & '" & Me.txtBox _
& "' WHERE MyTable.RecordId = " & Me.txtRecordID;"

You could use the OpenArgs argument of the OpenForm method to pass the ID to
the popup or if the form that opens the pop up is the only form that uses the
popup, you can reference it directly by referring to the opening form:

=Forms![MyOpeningForm]![txtRecordID]

I'm not sure I understand where you are setting the Yes/No. If it is in the
pop up when you update the record, you could do it in the SQL above. And, if
you have passed the values to the popup using the OpenARgs, you could
reference those values to update your table or, using the example above,
reference the form that opened the Popup.

Sorry if this is not too specific, but I guess I don't have a clear picture
of what all is going on.

Thanks for the reply Klatuu,

[quoted text clipped - 18 lines]

Bill

 

[Guest]

Okay, this is not that hard. It could be done without a popup by putting the
toggle buttons on your main form or you could use the popup.

The main issue here is not checking or unchecking the field in the table,
the trick will be keeping the toggle buttons in sync with the data. To to
that, I think you could put some code in the current event to set the toggle
button to reflect what is in the table.

Sorry for the confusion. The yes/no is in the table to be updated, I thought
a setting check box to "true" statement would be necessary. I do show a
checkbox in the the datasheet form with every record just for user reference.
Even though it is not an updatable field being the behind query is based on
two Totals queries in a union query, I was looking for the user to be able to
click the checkbox which would open the pop up form (if not I can use another
field with a double click). The pop up has two command buttons one for yes
and another one for no, if the user would like to update the record. No
closes the pop up and returns to orginal form. The Yes button on a click is
what I would like to handle the update to the underlying table and then
requery.

The click on the form's checkbox may not be possible unless I can get around
the Access user error of trying to update a nonupdatable query.

Thanks,

Bill

I believe there is a syntax error in my originla post (note I said it was
untested)
Try this:
strSQL = "UPDATE MyTable SET MyTable.SomeField = & '" & Me.txtBox _
& "' WHERE MyTable.RecordId = " & Me.txtRecordID;"

You could use the OpenArgs argument of the OpenForm method to pass the ID to
the popup or if the form that opens the pop up is the only form that uses the
popup, you can reference it directly by referring to the opening form:

=Forms![MyOpeningForm]![txtRecordID]

I'm not sure I understand where you are setting the Yes/No. If it is in the
pop up when you update the record, you could do it in the SQL above. And, if
you have passed the values to the popup using the OpenARgs, you could
reference those values to update your table or, using the example above,
reference the form that opened the Popup.

Sorry if this is not too specific, but I guess I don't have a clear picture
of what all is going on.

Thanks for the reply Klatuu,

[quoted text clipped - 18 lines]

Bill

 

[Bill Fischer via AccessMonster.com]

Thanks again for the reply. I would prefer to have the pop-up in that manner,
it would be in the user face.

By default, the subform has all records unchecked due to filtering on the
data source query. So really is a non-issue to deselect or uncheck the
underlying table. This field is to remove the record from the query, hence
the pop-up to verify user wants to perform the function.

Being on a large learning curve for access. Can you give me an idea what the
code would look like to keep the record ID in synch.

Bill

Okay, this is not that hard. It could be done without a popup by putting the
toggle buttons on your main form or you could use the popup.

The main issue here is not checking or unchecking the field in the table,
the trick will be keeping the toggle buttons in sync with the data. To to
that, I think you could put some code in the current event to set the toggle
button to reflect what is in the table.

Sorry for the confusion. The yes/no is in the table to be updated, I thought
a setting check box to "true" statement would be necessary. I do show a

[quoted text clipped - 41 lines]

 

[Guest]

You could use a DLookup to find the correct record and return the current
value of the field, then set the option group where the toggle buttons are to
the appropriate value:

varCheckValue = (DLookup("[YesNoField], "MyTableName", "[RecordID] = " _
& Me.txtRecordID)
If Not IsNull(varCheckValue) Then
Me.opgYesNo = varCheckValue
End If

Thanks again for the reply. I would prefer to have the pop-up in that manner,
it would be in the user face.

By default, the subform has all records unchecked due to filtering on the
data source query. So really is a non-issue to deselect or uncheck the
underlying table. This field is to remove the record from the query, hence
the pop-up to verify user wants to perform the function.

Being on a large learning curve for access. Can you give me an idea what the
code would look like to keep the record ID in synch.

Bill

Okay, this is not that hard. It could be done without a popup by putting the
toggle buttons on your main form or you could use the popup.

The main issue here is not checking or unchecking the field in the table,
the trick will be keeping the toggle buttons in sync with the data. To to
that, I think you could put some code in the current event to set the toggle
button to reflect what is in the table.

Sorry for the confusion. The yes/no is in the table to be updated, I thought
a setting check box to "true" statement would be necessary. I do show a

[quoted text clipped - 41 lines]

Bill