Hello!
I have been reading the many threads posted here and it has helped me resolve numerous problems in Access as a new user. Thank you very much!
I have one problem with subforms. I have one bound main form and two bound subforms: one subform is used to display existing records in datasheet view while another is used to enter new records in form view, but this latter subform for new records will not save automatically, nor will it save by using the me.refresh or me.dirty=true in the unload event of the subform control. The parent child relationship is intact. I can even see the primary key of the new row in the subform but it does not appear in the table that the subform is based on. This primary key adds by one automatically.
I have struggled and finally made work a combo box in the main form that finds existing records but also is able to add new records when the value entered is not on the list. Below is the code I used. I am not sure if this code is overriding Access's save ability of the subform?
Private Sub Combo316_AfterUpdate()
Dim n As String
gblvariable = cbobox
Me.Requery
n = DLookup("ID", "[Customers Extended]", "[Customer Name] = '" & Me!Combo316 & "'")
DoCmd.SearchForRecord , Customers, acFirst, "[ID] = " & Str(Nz(n, 0))
Forms![Case Details].Form1.Form.Recordset.AddNew
[Suffix].Locked = False
[First Name].Locked = False
[Last Name].Locked = False
[E-mail address].Locked = False
[mobile phone].Locked = False
[Home Phone].Locked = False
[Address].Locked = False
[street name].Locked = False
[street type].Locked = False
[apt no].Locked = False
End Sub
Private Sub Combo316_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim F As String
Dim L As String
TempVars.Add "Space", InStrRev(NewData, " ")
F = Left(NewData, [TempVars]![Space] - 1)
L = Mid(NewData, [TempVars]![Space] + 1)
On Error Resume Next
intAnswer = msgbox("The customer " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Customer Care Database")
If intAnswer = vbYes Then
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Customers", dbOpenDynaset)
Rs.AddNew
Rs![Last Name] = L
Rs![First Name] = F
Rs.Update
Rs.Requery
DoCmd.Save
Response = acDataErrAdded
End If
End Sub
I have been reading the many threads posted here and it has helped me resolve numerous problems in Access as a new user. Thank you very much!
I have one problem with subforms. I have one bound main form and two bound subforms: one subform is used to display existing records in datasheet view while another is used to enter new records in form view, but this latter subform for new records will not save automatically, nor will it save by using the me.refresh or me.dirty=true in the unload event of the subform control. The parent child relationship is intact. I can even see the primary key of the new row in the subform but it does not appear in the table that the subform is based on. This primary key adds by one automatically.
I have struggled and finally made work a combo box in the main form that finds existing records but also is able to add new records when the value entered is not on the list. Below is the code I used. I am not sure if this code is overriding Access's save ability of the subform?
Private Sub Combo316_AfterUpdate()
Dim n As String
gblvariable = cbobox
Me.Requery
n = DLookup("ID", "[Customers Extended]", "[Customer Name] = '" & Me!Combo316 & "'")
DoCmd.SearchForRecord , Customers, acFirst, "[ID] = " & Str(Nz(n, 0))
Forms![Case Details].Form1.Form.Recordset.AddNew
[Suffix].Locked = False
[First Name].Locked = False
[Last Name].Locked = False
[E-mail address].Locked = False
[mobile phone].Locked = False
[Home Phone].Locked = False
[Address].Locked = False
[street name].Locked = False
[street type].Locked = False
[apt no].Locked = False
End Sub
Private Sub Combo316_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim F As String
Dim L As String
TempVars.Add "Space", InStrRev(NewData, " ")
F = Left(NewData, [TempVars]![Space] - 1)
L = Mid(NewData, [TempVars]![Space] + 1)
On Error Resume Next
intAnswer = msgbox("The customer " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Customer Care Database")
If intAnswer = vbYes Then
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Customers", dbOpenDynaset)
Rs.AddNew
Rs![Last Name] = L
Rs![First Name] = F
Rs.Update
Rs.Requery
DoCmd.Save
Response = acDataErrAdded
End If
End Sub