Its OK, i dont have any exciting news about new positions given to users unfortunately, just a problem thats all. but hey, with all of these new "in charge " people, isnt it great?

seriously,

a straight forward issue.

My query has the following fields

MonthNum ( number )
CurrentPeriod ( yes/No )

its work is simple. MonthNum criteria is set to forms!mainform!monthnumtxt
( this is currently showing as 9 as it is this month 8 + 1 to get the next month number )

when the form is open, i can run the query and get the tickbox. i can tick the tickbox, close the query and the value is saved.

i want to automate this from a button which will select the next period.

Set db = CurrentDb
Set rs = db.OpenRecordset("UpdateMonthQry", dbOpenDynaset)
rs.Edit
rs!CurrentPeriod = True
rs.Update

this will not update the query. any reason why?

alternatively, i could update the table but im not sure how to filter this to the number in the text field.

could this even be updated via SQL

Select CurrentPeriod From UpdateMonthlyQry Where Forms!mainform!textbox

but im nto good with SQL

my vb is right. right?

thanks,

Nigel

Set db = CurrentDb
Set rs = db.OpenRecordset("UpdateMonthQry", dbOpenDynaset)
rs.Edit
rs!CurrentPeriod = True
rs.Update

this will not update the query. any reason why?


This should update the value in the first record of the recordset which may already have this field set to "True"

To change the values in other records you would need to have code to find the record you wish to amend.

Hi Rabbie,

the query will only ever return 1 set ofrecords as only one box will be ticked. the tick box needs to move don the list each month to coincide with the actual month.

i have a code that runs before this to clear the text boxes but there is still data in the table for other references.

another odd thing.

to test, i add this directly under rs = db.OpenRecordset("table", dbOpenDynaset)
msgbox("true")
Exit sub

i didnt get the text box

i have also changed the name of the query but nothing. the table feild typ is the same as the form textbox which are both set to number. i have also tried removing the format from the form textbox and setting the table field to text but again, nothing.

seems strnage how i can update the table with an update query yet not with a normal recordset.


Nigel

Hi,

an update query did it.

ever feel like you fix one problem, and 5 more appear!!

regs,

nigel