Command button to check subform field? 2

[gabber4858]

I have a main form called Program Detail with a subform called Program Item Details Sub Form. I created 2 buttons in the main form to either check or uncheck the "Print" field in the subform. I did have this working, but it checked or unchecked all the records in the Program Item Details Table.

I am now trying to get the code to only check/uncheck the Print box for the current Program in the mainform. This is what I have been able to piece together so far:

Private Sub Command34_Click()
Dim rs As DAO.Recordset
Set rs = Forms![Program Detail]![Program Item Details Sub Form].Form![Print].RecordsetClone
Do While Not rs.EOF
rs.Edit
rs!Print = True
rs.Update
rs.MoveNext
Loop
Set rs = Nothing
End Sub

When I click the button, I get an error 2465 .....can;t find the field "Program Item Details Sub Form".

My questions are: What am I doing wrong and how close am I? Any help would be greatly appreciated!

 

[dhookom]

Command34_click()? Do yourself a favor and find and use a naming convention that gives your controls names like "cmdUpdatePrint" or similar.

If [Print] is a control, it doesn't have a RecordsetClone. The subform has a RecordsetClone.

Based on your subform's recordset, how would you define "current Program"? Is Program a field in the subform? What is its data type?

Duane
Hook'D on Access
MS Access MVP

 

[gabber4858]

Thanks for the tip about naming conventions. This form is based off of 2 tables. Programs (Customer Information) and Program Item Details (Order detail information for that Customer). Each entry in the Program Item Details has a checkbox called "Print". I put this in so that I can select/unselect certain items so the will not show up in the main report.

So the subform "Program Item Details" is linked to the main form "Programs" on the ProgramID. The Print control is a in the subform, not the main form. I want to be able to check/uncheck all the ordered items for the currently displayed Program (Customer).

My original attempt for one of the buttons was:

Private Sub Command35_Click()
Dim strSql As String
strSql = "UPDATE [Program Item Details] SET [Print] = True WHERE [Print] = False;"
DBEngine(0)(0).Execute strSql, dbFailOnError
Requery
End Sub

This works great, but it does every record in the table. I need a way to make it only update the records that have the same ProgramID as the text box in the open form. Does this make sense? I don't know what im doing, but this is probably an easy thing to do (for more experienced users)?

 

[dhookom]

Assuming ProgramID is numeric, try:

Private Sub Command35_Click()
  Dim strSql As String
  strSql = "UPDATE [Program Item Details] " & _
    "SET [Print] = True "  _
    "WHERE [Print] = False AND ProgramID = " & Me.ProgramID
  DBEngine(0)(0).Execute strSql, dbFailOnError
  Me.Requery
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[gabber4858]

Dohookom,

I get a compile error, syntax error. I pretty much copy and pasted your code right in. I will keep playing with this and see if I can figure it out.

 

[TheAceMan1]

How are ya gabber4858 . . .

[blue]dhookom's[/blue] code is sound except or the [blue]Me.Requery[/blue]. When you requery the form/subform returns to the 1st record as if you just opened the form. At the time, you may be on any record besides the 1st. You need a way to go back to that record. The following assumes [blue]PKID[/blue] is the primarykey of the main form and assumes it is numeric:

[blue]   Dim db As DAO.Database, Sql As String
   Dim hldID As Long, Cri As String
   
   Set db = CurrentDb
   Sql = "UPDATE [Program Item Details] " & _
         "SET [Print] = True " [red][b]&[/b][/red] _
         "WHERE [Print] = False AND [ProgramID] = " & Me.ProgramID
   hldID = Forms![Program Detail]![purple][b]PKID[/b][/purple]
   
   db.Execute strSql, dbFailOnError
   Me.Requery
   Cri = [PKID] = hldID
   Me.Recordset.FindFirst Cri[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

gabber4858 . . .

Just a side note here ... get rid of spaces in your naming convention and you can remove the brackets! Also keep your names as short as possible for readability. My reason is ... if your names are long enough (espcially if they repeat themselves in code) you'll have to decipher instead of read! Make your code easy for you to read ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[gabber4858]

TheAceMan1....I am a little frustrated, but I appreciate your help. I am still getting errors, and am fully appreciating dhookom and your advice on naming conventions. I think I need to start over... in the one table I have "Program ID" and in the other I have "ProgramID".

The other main issue I think is I do not show the Program ID in the main form, and I do not show the ProgramID in the subform. This is definetly a painful way to learn a lesson.

I have attached a link with screen shots. Thanks again, I really didn't think this was going to be this hard.... lol