G
Guest
The code shown below is the compliation of alot of people's help on this
discussion board, and is very much appreciated. While I am close, things
still are not working right. While it first appeared to me that I was able
to add values on the fly to the combo box on my main form (Patterns), but not
to its related subform (Fabrics), upon further exploration, I realized that I
am not adding new values to the main form Patterns. The underlying table
called "Patterns" Has the Fields: Pattern Name, MfgID, PatternID. PatternID
is an autonumber. When I use the code below, what happens is a new
PatternName is accepted as a replacement for the PatternName that was on the
form before I went to add the new value through the combobox. If I started
with 10 Patterns, I still end up with 10 patterns although the name of one of
them has been changed to the value I added on the fly throught the combo box.
Incidentally, the form works fine if I just add a new pattern. Both form
and subform accept the new info and I increase from 10 patterns to 11 along
with adding however many new fabrics (Subform) are needed. The autonumbers
work just fine to create a new unique PatternID and New FabricIDs in the
subform.
I don't know alot about programing, but do I need to create a new record
somewhere, should/could the word INSERT create a new record. Is there
another line that is missing here?
Thanks so much
Lele
Private Sub PatternName_NotInList(NewData As String, Response As Integer)
Dim sqlAddPattern, sqlAddFabric As String, strMessage As String
Dim UserResponse As Integer
Dim ctrl As Control
On Error GoTo Err_Handler
Set ctrl = Me.ActiveControl
Beep
strMessage = "Do you want to add Pattern '" & NewData & "' to the list?"
UserResponse = MsgBox(strMessage, vbYesNo + vbQuestion, "New Pattern")
If UserResponse = vbYes Then
If Not IsNull(Me.MfgID) Then
sqlAddPattern = "INSERT INTO Patterns (PatternName, MfgID)" & _
"VALUES(""" & NewData & """,""" & Me.MfgID & """)"
'DoCmd.GoToControl , Forms!PatternsFabrics!SubFabrics,
'DoCmd.GoToControl , Forms!PatternsFabrics!SubFabrics, , , acNewRec
'sqlAddFabric = "Insert Into Fabrics (PatternID)Values (""" &
PatternID & """)"
CurrentDb.Execute sqlAddPattern, dbFailOnError
Response = acDataErrAdded
Else
strMessage = "A Fabric Company must be selected first."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
Exit_Here:
Exit Sub
Err_Handler:
Response = acDataErrContinue
strMessage = Err.Description & " (" & Err.Number & ")"
MsgBox strMessage, vbExclamation, "Error"
Resume Exit_Here
End Sub
--
discussion board, and is very much appreciated. While I am close, things
still are not working right. While it first appeared to me that I was able
to add values on the fly to the combo box on my main form (Patterns), but not
to its related subform (Fabrics), upon further exploration, I realized that I
am not adding new values to the main form Patterns. The underlying table
called "Patterns" Has the Fields: Pattern Name, MfgID, PatternID. PatternID
is an autonumber. When I use the code below, what happens is a new
PatternName is accepted as a replacement for the PatternName that was on the
form before I went to add the new value through the combobox. If I started
with 10 Patterns, I still end up with 10 patterns although the name of one of
them has been changed to the value I added on the fly throught the combo box.
Incidentally, the form works fine if I just add a new pattern. Both form
and subform accept the new info and I increase from 10 patterns to 11 along
with adding however many new fabrics (Subform) are needed. The autonumbers
work just fine to create a new unique PatternID and New FabricIDs in the
subform.
I don't know alot about programing, but do I need to create a new record
somewhere, should/could the word INSERT create a new record. Is there
another line that is missing here?
Thanks so much
Lele
Private Sub PatternName_NotInList(NewData As String, Response As Integer)
Dim sqlAddPattern, sqlAddFabric As String, strMessage As String
Dim UserResponse As Integer
Dim ctrl As Control
On Error GoTo Err_Handler
Set ctrl = Me.ActiveControl
Beep
strMessage = "Do you want to add Pattern '" & NewData & "' to the list?"
UserResponse = MsgBox(strMessage, vbYesNo + vbQuestion, "New Pattern")
If UserResponse = vbYes Then
If Not IsNull(Me.MfgID) Then
sqlAddPattern = "INSERT INTO Patterns (PatternName, MfgID)" & _
"VALUES(""" & NewData & """,""" & Me.MfgID & """)"
'DoCmd.GoToControl , Forms!PatternsFabrics!SubFabrics,
'DoCmd.GoToControl , Forms!PatternsFabrics!SubFabrics, , , acNewRec
'sqlAddFabric = "Insert Into Fabrics (PatternID)Values (""" &
PatternID & """)"
CurrentDb.Execute sqlAddPattern, dbFailOnError
Response = acDataErrAdded
Else
strMessage = "A Fabric Company must be selected first."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
Exit_Here:
Exit Sub
Err_Handler:
Response = acDataErrContinue
strMessage = Err.Description & " (" & Err.Number & ")"
MsgBox strMessage, vbExclamation, "Error"
Resume Exit_Here
End Sub
--