Updating several rows at once on a continous form

[Moss100]

Hello all,

I have a continuous form called FORM1

This form has a field called ID and FIELD 1

I would like to update all records where ID is equal to the value of a textbox on the form (TEXTBOX1).

There will usually be around 5 records.

I have tried an update query and this works ok on its own, maunaully entering an ID number, but it does not work when looking for the value from the form. It does not crash, but it does not update either.

I am using: [forms]![form1]![textbox1]

Is there a better way to update these records?

I'm not much use at code, but found this below:

"Update tblsomething set tblsomething.fieldname ="new value" Where tblsomething.[jobs_D]=" & forms("formname").[jobs_ID]

Is it possible to use something like this where it could update all the records on the form - or is it restricted to the current record.

Thanks for any help im struggling on this one!




but when I try and get a value off the form

 

[dhookom]

Give us the significant table and field names as well as data types. Then provide the names of the form and controls on the form that are significant.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Thanks dhookom -

Ok ignore the above names this is what I have.

Table name = ATTENDEE_DETAILS
Form Name (this is a continuous form)= CONFIRMATION_SCREEN
Field Name (this is set to yes/no)= ATTENDEE_CONTACTED_FOR_CONFIRM
Field Name (this is set to yes/no)= ATTENDEE_MARKET_BY_SMS

I have a button called : btn_SEND and on the after update event I would select those in which ATTENDEE_MARKET_BY_SMS is set to YES and then update the fields ATTENDEE_CONTACTED_FOR_CONFIRM to YES also.

If the field ATTENDEE_MARKET_BY_SMS was null then no updates would be required for that record.

Many thanks for your help and interest

Regards Mark

 

[dhookom]

I would use something like this in the code of the command button.

Dim strSQL as String
strSQL = "UPDATE ATTENDEE_DETAILS SET ATTENDEE_CONTACTED_FOR_CONFIRM = -1 WHERE ATTENDEE_MARKET_BY_SMS = -1"
CurrentDb.Execute strSQL, dbFailOnError
MsgBox "Records updated"

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

That is brilliant thank you -

Just one extra if you could - i forgot to say that on the form I have a field in the header called - LINK_REF

The field in the records is called APPOINTMENTS_LINK_REF

Can you show me how to just update the records on the form and not the whole table using the field APPOINTMENTS_LINK_REF

I thought it would be this....


Private Sub Command86_DblClick(Cancel As Integer)
Dim strSQL As String
strSQL = "UPDATE ATTENDEE_DETAILS SET ATTENDEE_CONTACTED_FOR_CONFIRM = -1 WHERE ATTENDEE_MARKET_BY_SMS = -1" And me.APPOINTMENTS_LINK_REF
= me.LINK_REF
CurrentDb.Execute strSQL, dbFailOnError
MsgBox "Records updated"
End Sub


But no luck. Much appreciated, Mark

 

[dhookom]

Moss100,
Please take advantage of TGML. It's really easy. Just highlight a section of your post and click a button.

Private Sub Command86_DblClick(Cancel As Integer)
  Dim strSQL As String
   [COLOR=#4E9A06]'Assuming APPOINTMENTS_LINK_REF is numeric[/color]
  strSQL = "UPDATE ATTENDEE_DETAILS SET ATTENDEE_CONTACTED_FOR_CONFIRM = -1 " & _
    "WHERE ATTENDEE_MARKET_BY_SMS = -1 And APPOINTMENTS_LINK_REF =  " & Me.LINK_REF
  CurrentDb.Execute strSQL, dbFailOnError
  MsgBox "Records updated"
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Cracker! Thank you