How should I go about coding this form? Edit multiple fields in subform at once.

[wakamoe]

Hello yall,

I have an idea for a form. However being new to VBA coding, I have not idea how to proceed.

I want to create a form to allow me to change multiple fields of multiple entries as once. However, only those entries that is checked on the form will be changed.

After the entries are ticked by the user, the user will then proceed to type in the textboxes below the subform to whatever they want to update the fields to.

After clicking the update button, the subform (table) will update with the changes and all checkboxes will be reset (not checked).

Here is a sketch up I did of how the form would look like.

imgur.com/a/KUeBP

Thank you, any advise is greatly appreciated.

 

[JHB]

Use an update query, which is executed when the "Update" button is clicked.
In the click event for the button place the below code, (remember to change the table name and the name of the controls):

  CurrentDb.Execute("UPDATE [YourTableName] " _
  & "SET [checkin_time]=" & Me.[NameOfYourCheckInControl] & ", [checkout_time]=" & Me.[NameOfYourCheckOutControl] & ", [subject]=" & Me.[NameOfYourSubjectControl] & ", [teacher]=" & Me.[NameOfYourTeacherControl] & ", [yes_no]=0 " _
  & "WHERE [yes_no] = -1")
  Me.Requery

 

[wakamoe]

Use an update query, which is executed when the "Update" button is clicked.
In the click event for the button place the below code, (remember to change the table name and the name of the controls):

  CurrentDb.Execute("UPDATE [YourTableName] " _
  & "SET [checkin_time]=" & Me.[NameOfYourCheckInControl] & ", [checkout_time]=" & Me.[NameOfYourCheckOutControl] & ", [subject]=" & Me.[NameOfYourSubjectControl] & ", [teacher]=" & Me.[NameOfYourTeacherControl] & ", [yes_no]=0 " _
  & "WHERE [yes_no] = -1")
  Me.Requery

Thank you so much! It works fine for the most part expect for 1 problem. I am not allowed to leave a blank in one of the text boxes. Do you have any idea how we can overcome this issue?

EDIT: This is the current code with all the subject changed

Private Sub updatebutton_Click()
CurrentDb.Execute ("UPDATE [table1] " _
& "SET [checkin_time]=" & Me.[control1] & ", [checkout_time]=" & Me.[Control2] & ", [subject]=" & Me.[Control3] & ", [teacher]=" & Me.[Control4] & ", [yes_no]=0 " _
& "WHERE [yes_no] = -1")
Me.Requery
[Child1].Form.Requery
End Sub

Also, isit possible for everything entry inbetween two checkboxes to be ticked automatically?

What I mean by this is that, if the 2nd and 100th checkbox is ticked, everything else inbetween would be ticked as well.

 

[JHB]

Use the NZ function:

... & NZ(Me.[control1],Null) & ...

You use very poor names for the controls.

 

[wakamoe]

Use the NZ function:

... & NZ(Me.[control1],Null) & ...

You use very poor names for the controls.

Yea... haha, thank you once again for your help. This is a mock up. I am trying to get it to work before moving it to another database where all the names would be changed.

 

[wakamoe]

Use the NZ function:

... & NZ(Me.[control1],Null) & ...

You use very poor names for the controls.

Hi!

I went about adding in the "NULL" so that it will allow me to leave the textbox blank but it still does not work. Do you have any idea why?

Option Compare Database

Private Sub updatebutton_Click()
CurrentDb.Execute ("UPDATE [table1] " _
& "SET [checkin_time]=" & Nz(Me.[control1], Null) & ", [checkout_time]=" & Nz(Me.[Control2], Null) & ", [subject]=" & Nz(Me.[Control3], Null) & ", [teacher]=" & Nz(Me.[Control4], Null) & ", [yes_no]=0 " _
& "WHERE [yes_no] = -1")


Me.Requery
[Child1].Form.Requery
End Sub



Also, is there a way to checkmultiple checkbox at once? Meaning, ticking the 2nd hand 50th checkbox and everything else inbetween will get checked as well or something along those lines.

As my idea behind the database is still the same, I want to edit multiple fields of multiple entries at once BUT I just noticed that at any point of time, I might need to check 100+ entries out of a thousand and ticking the checkboxes individually is too tedious.

Do you have any idea how to overcome this problem?

 

[JHB]

... but it still does not work.

What does still not work, (it is to weak a description), do you get any error message and number, then show it.
Try the below:

CurrentDb.Execute ("UPDATE [table1] " _
  & "SET [checkin_time]=#" & Nz(Me.[control1], Null) & "#,  [checkout_time]=#" & Nz(Me.[Control2], Null) & "#, [subject]='"  & Nz(Me.[Control3], Null) & "', [teacher]='" &  Nz(Me.[Control4], Null) & "', [yes_no]=0 " _
  & "WHERE [yes_no] = -1")

Else post your database with some sample data, zip it.

...
Also, is there a way to checkmultiple checkbox at once? Meaning, ticking the 2nd hand 50th checkbox and everything else inbetween will get checked as well or something along those lines.

How would you determine that everything between 2 and 50 has to be filled out, then it could be correct that only 2 and 50 are ticked!
Maybe you could use 2 text controls (from - to) and a button. Then use an update query to mark them.

 

[wakamoe]

What does still not work, (it is to weak a description), do you get any error message and number, then show it.
Try the below:

CurrentDb.Execute ("UPDATE [table1] " _
  & "SET [checkin_time]=#" & Nz(Me.[control1], Null) & "#,  [checkout_time]=#" & Nz(Me.[Control2], Null) & "#, [subject]='"  & Nz(Me.[Control3], Null) & "', [teacher]='" &  Nz(Me.[Control4], Null) & "', [yes_no]=0 " _
  & "WHERE [yes_no] = -1")

Else post your database with some sample data, zip it.

How would you determine that everything between 2 and 50 has to be filled out, then it could be correct that only 2 and 50 are ticked!
Maybe you could use 2 text controls (from - to) and a button. Then use an update query to mark them.

Thank you very much for the reply. I should have elaborated on the problem.

I am still not able to leave a blank in the text box. When a textbox is left blank, an error appeared saying that there is an error in the update statement.

As for the from-to update query. I will try to find a solution on how to do that!

 

[JHB]

Now it should work, I've made a solution for you regarding the from-to.

 

[wakamoe]

Now it should work, I've made a solution for you regarding the from-to.

Awesome! thank you very much for solving the NULL issue and even doing up the solution for the from-to.

 

[JHB]

You're welcome, good luck.