Please Help

  • Thread starter Thread starter Joanie
  • Start date Start date
J

Joanie

I have a form and a subform. The form is called updates
and it's control source is a table called Customer
Information. The subform is called System Information and
it's control source was a table called System
Information. In an effort to normalize my tables, I had
to create more than one table from System Information. So
I now, have the System Information form's control source
as Service Card Query. When I do this the forms no longer
work appropriately. When I clicked add a new system, the
next available system_id number would appear. Now, that
the control source has changed from the System Information
table to the Service Cards Query, it no longer works.
Here's the following code behind my form-where do I need
to change the control source so that the 2 forms interact
correctly???? I'm at a loss and would greatly appreciate
any insight or advice....Thanks!!!

Option Explicit
Option Compare Database

Private Sub CustomerID_AfterUpdate()

'Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustID]='" & Me![CustomerID] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub Delete_Click()
On Error GoTo Err_Delete_CLick
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_Delete_Click:
Exit Sub

Err_Delete_CLick:
MsgBox Err.Description
Resume Exit_Delete_Click
End Sub

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

DoCmd.GoToRecord , , acNewRec
Exit_Add_Record_Click:
Exit Sub
Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click
End Sub

Private Sub Form_Current()
Dim res As Integer, strSystemID As String
If Me.NewRecord And Not IsNull(Me.Parent.ID) Then
'res=DMax("System_ID", "[System Information]") +1
res = Me.Recordset.RecordCount
strSystemID = Me.Parent.ID + "-" + Format(Str(res +
1), "00")
Me.System_ID = strSystemID
End If
End Sub

Private Sub Form_Load()

End Sub

Private Sub Open_Service_Card_Click()
On Error GoTo Err_Open_Service_Card_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Service Cards"
stLinkCriteria = "[System_ID]=" & "'" & Me![System_ID]
& "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Open_Service_Card_Click:
Exit Sub

Err_Open_Service_Card_Click:
MsgBox Err.Description
Resume Exit_Open_Service_Card_Click
End Sub
Private Sub Undo_Record_Click()
On Error GoTo Err_Undo_Record_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,
acMenuVer70
Exit_Undo_Record_Click:
Exit Sub
Err_Undo_Record_Click:
MsgBox Err.Description
Resume Exit_Undo_Record_Click

End Sub
Private Sub Refresh_Click()
On Error GoTo Err_Refresh_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Refresh_Click:
Exit Sub

Err_Refresh_Click:
MsgBox Err.Description
Resume Exit_Refresh_Click
End Sub
 
Hi Joanie!

Your main form have table Customer as a recordsource, right!? And your
subform have query Service Card Query as a rowsource right, !?

What field in query Service Card Query is linked to table Customer and how´s
the relationship between the involved tables?

What I´m after is the linking fields between table Customer and query
Service Card Query and if this is correct!

// Niklas


Joanie said:
I have a form and a subform. The form is called updates
and it's control source is a table called Customer
Information. The subform is called System Information and
it's control source was a table called System
Information. In an effort to normalize my tables, I had
to create more than one table from System Information. So
I now, have the System Information form's control source
as Service Card Query. When I do this the forms no longer
work appropriately. When I clicked add a new system, the
next available system_id number would appear. Now, that
the control source has changed from the System Information
table to the Service Cards Query, it no longer works.
Here's the following code behind my form-where do I need
to change the control source so that the 2 forms interact
correctly???? I'm at a loss and would greatly appreciate
any insight or advice....Thanks!!!

Option Explicit
Option Compare Database

Private Sub CustomerID_AfterUpdate()

'Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustID]='" & Me![CustomerID] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub Delete_Click()
On Error GoTo Err_Delete_CLick
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_Delete_Click:
Exit Sub

Err_Delete_CLick:
MsgBox Err.Description
Resume Exit_Delete_Click
End Sub

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

DoCmd.GoToRecord , , acNewRec
Exit_Add_Record_Click:
Exit Sub
Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click
End Sub

Private Sub Form_Current()
Dim res As Integer, strSystemID As String
If Me.NewRecord And Not IsNull(Me.Parent.ID) Then
'res=DMax("System_ID", "[System Information]") +1
res = Me.Recordset.RecordCount
strSystemID = Me.Parent.ID + "-" + Format(Str(res +
1), "00")
Me.System_ID = strSystemID
End If
End Sub

Private Sub Form_Load()

End Sub

Private Sub Open_Service_Card_Click()
On Error GoTo Err_Open_Service_Card_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Service Cards"
stLinkCriteria = "[System_ID]=" & "'" & Me![System_ID]
& "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Open_Service_Card_Click:
Exit Sub

Err_Open_Service_Card_Click:
MsgBox Err.Description
Resume Exit_Open_Service_Card_Click
End Sub
Private Sub Undo_Record_Click()
On Error GoTo Err_Undo_Record_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,
acMenuVer70
Exit_Undo_Record_Click:
Exit Sub
Err_Undo_Record_Click:
MsgBox Err.Description
Resume Exit_Undo_Record_Click

End Sub
Private Sub Refresh_Click()
On Error GoTo Err_Refresh_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Refresh_Click:
Exit Sub

Err_Refresh_Click:
MsgBox Err.Description
Resume Exit_Refresh_Click
End Sub
Click to expand...
 
Back
Top