Updating Data to a sub form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form "Frm_Header" which has on it a sub form "Frm_Header_Sub", which
is continuous.
On the Frm_Header, there is a field "Pass", when this field is changed I
want it to automatically change the value in a field "Pass_Sub" on any of the
records in the sub form Frm_Header_Sub.
The forms are linked by Master Field "ID" and Child Field "ID_Sub".
I have tried the following code on the On Click event of a command Buttton
"Update_Btn"

Private Sub Update_Btn_Click()

Update.Tbl_Header_Sub
Set Tbl_Header_Sub.Pass_Sub = Tbl_Header.Pass
WHERE Tbl_Header_Sub.ID_Sub = Tbl_Header.ID

End Sub

This is returning an error message "Variable not defined" and highlighting
Update

This is probably something simple that I have overlooked, but I would
appreciate help as I am quite new to Access97, and usually use macros to
carry out actions, but I want to learn more code.

Thanks in advance

Joe
 
hi Joe,

Joe said:
Private Sub Update_Btn_Click()

Update.Tbl_Header_Sub
Set Tbl_Header_Sub.Pass_Sub = Tbl_Header.Pass
WHERE Tbl_Header_Sub.ID_Sub = Tbl_Header.ID

End Sub

This is returning an error message "Variable not defined" and highlighting
Update
Click to expand...
Access has no LINQ-style language. Use the following:

Private Sub Update_Btn_Click()

On Local Error Goto LocalError

Dim SQL As String

SQL = "UPDATE Tbl_Header_Sub " & _
"SET Pass_Sub = " & Me![Pass] & " " & _
"WHERE ID_Sub = " & Me![ID]

CurrentDb.Execute SQL, dbFailOnError
SubFormCtrl.Form.Requery

Exit Sub

LocalError:
MsgBox Err.Description

End Sub

or

Private Sub Update_Btn_Click()

Dim rs As DAO.Recordset

' Use the name of the subform _control_. Not
' the name of the subform.
Set rs = NameOfSubformControl.Form.RecordsetClone

Do While Not rs.Eof
rs.Edit
rs![Pass_Sub] = Me![Pass]
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub
This is probably something simple that I have overlooked, but I would
appreciate help as I am quite new to Access97
Click to expand...
Access 2007 is the latest version.


mfG
--> stefan <--
 
Hi Stefan

Thanks for your reply, couldnt wait to use it, :(, got a problem
when I used the code (your first suggestion) I am getting another Variable
Not Defined message and the text "SubFormCtrl" is being highlighted.

Sorry for being a pain, I appreciate your help

Thanks

Joe

Stefan Hoffmann said:
hi Joe,

Joe said:
Private Sub Update_Btn_Click()

Update.Tbl_Header_Sub
Set Tbl_Header_Sub.Pass_Sub = Tbl_Header.Pass
WHERE Tbl_Header_Sub.ID_Sub = Tbl_Header.ID

End Sub

This is returning an error message "Variable not defined" and highlighting
Update
Click to expand...
Access has no LINQ-style language. Use the following:

Private Sub Update_Btn_Click()

On Local Error Goto LocalError

Dim SQL As String

SQL = "UPDATE Tbl_Header_Sub " & _
"SET Pass_Sub = " & Me![Pass] & " " & _
"WHERE ID_Sub = " & Me![ID]

CurrentDb.Execute SQL, dbFailOnError
SubFormCtrl.Form.Requery

Exit Sub

LocalError:
MsgBox Err.Description

End Sub

or

Private Sub Update_Btn_Click()

Dim rs As DAO.Recordset

' Use the name of the subform _control_. Not
' the name of the subform.
Set rs = NameOfSubformControl.Form.RecordsetClone

Do While Not rs.Eof
rs.Edit
rs![Pass_Sub] = Me![Pass]
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub
This is probably something simple that I have overlooked, but I would
appreciate help as I am quite new to Access97
Click to expand...
Access 2007 is the latest version.


mfG
--> stefan <--
Click to expand...
 
Hi Stefan

Thanks for your solutions, I played about with them and came up with the
following, which works great

Private Sub Update_Btn_Click()
Dim db As DAO.Database
On Local Error GoTo LocalError

Dim SQL As String
Set db = CurrentDb()

SQL = "UPDATE Tbl_Header_Sub " & "SET Pass_Sub = '" & Me![Pass] & "' "_ &
"WHERE ID_Sub = " & Me![ID]
db.Execute SQL
Me.Refresh

Exit Sub

LocalError:
MsgBox Err.Description


Stefan Hoffmann said:
hi Joe,

Joe said:
Private Sub Update_Btn_Click()

Update.Tbl_Header_Sub
Set Tbl_Header_Sub.Pass_Sub = Tbl_Header.Pass
WHERE Tbl_Header_Sub.ID_Sub = Tbl_Header.ID

End Sub

This is returning an error message "Variable not defined" and highlighting
Update
Click to expand...
Access has no LINQ-style language. Use the following:

Private Sub Update_Btn_Click()

On Local Error Goto LocalError

Dim SQL As String

SQL = "UPDATE Tbl_Header_Sub " & _
"SET Pass_Sub = " & Me![Pass] & " " & _
"WHERE ID_Sub = " & Me![ID]

CurrentDb.Execute SQL, dbFailOnError
SubFormCtrl.Form.Requery

Exit Sub

LocalError:
MsgBox Err.Description

End Sub

or

Private Sub Update_Btn_Click()

Dim rs As DAO.Recordset

' Use the name of the subform _control_. Not
' the name of the subform.
Set rs = NameOfSubformControl.Form.RecordsetClone

Do While Not rs.Eof
rs.Edit
rs![Pass_Sub] = Me![Pass]
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub
This is probably something simple that I have overlooked, but I would
appreciate help as I am quite new to Access97
Click to expand...
Access 2007 is the latest version.


mfG
--> stefan <--
Click to expand...
 
Back
Top