First atempt at Not in list

  • Thread starter Thread starter Rob P
  • Start date Start date
R

Rob P

Hi

I am tring to adapt some code found in an MS Access book by John Viescas
must add very good training material

I have a subfrm frmWorkshopbooking and a combo box Combo67. This is bound
to a learner CSID. The combo box displays the learners name but stores the
CSID value

I have input the following code which works well except when returning to
the workshopbooking sub form the combo box still requires to be requeried.
Some attemps made are in as comment lines.

Any help much appricated



Private Sub Combo67_NotInList(NewData As String, Response As Integer)
Dim strIName As String, strLastN As String, strFirstN As String,
intSpace As Integer
Dim intReturn As Integer, varName As Variant
Dim Combo As Control
'pass the input name to the vaiable strIname
strIName = NewData

' check if space in input name
intSpace = InStr(strIName, " ")

If intSpace = 0 Then
MsgBox "enter full name seperted by a space"
Exit Sub

Else: strFirstN = Left(strIName, intSpace - 1)
strLastN = Mid(strIName, intSpace + 2)
End If

intReturn = MsgBox("Learner name " & strIName & " is not in the system.
Do you want to add this Learner?", vbQuestion + vbYesNo, "New Learner?")

If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frmNewLearner", _
datamode:=acFormAdd, _
windowmode:=acDialog, _
OpenArgs:=strIName

Response = acDataErrAdded

' Set Combo = Forms![frmworkshops]![frmworkshopbookings]![CSID]
' Me.Requery
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End If

Response = acDataErrDisplay

End Sub
 
Try an Else clause in the last If statement and move the last Response line.
If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frmNewLearner", _
datamode:=acFormAdd, _
windowmode:=acDialog, _
OpenArgs:=strIName

Response = acDataErrAdded

' Set Combo = Forms![frmworkshops]![frmworkshopbookings]![CSID]
' Me.Requery
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Click to expand...
Else
Response = acDataErrDisplay
End If
Click to expand...

As currently written, you're always executing the line "Response =
acDataErrDisplay" and, I suspect, that this would undo your line "Response =
acDataErrAdded".

--
Wayne Morgan
MS Access MVP


Rob P said:
Hi

I am tring to adapt some code found in an MS Access book by John Viescas
must add very good training material

I have a subfrm frmWorkshopbooking and a combo box Combo67. This is
bound to a learner CSID. The combo box displays the learners name but
stores the CSID value

I have input the following code which works well except when returning to
the workshopbooking sub form the combo box still requires to be requeried.
Some attemps made are in as comment lines.

Any help much appricated



Private Sub Combo67_NotInList(NewData As String, Response As Integer)
Dim strIName As String, strLastN As String, strFirstN As String,
intSpace As Integer
Dim intReturn As Integer, varName As Variant
Dim Combo As Control
'pass the input name to the vaiable strIname
strIName = NewData

' check if space in input name
intSpace = InStr(strIName, " ")

If intSpace = 0 Then
MsgBox "enter full name seperted by a space"
Exit Sub

Else: strFirstN = Left(strIName, intSpace - 1)
strLastN = Mid(strIName, intSpace + 2)
End If

intReturn = MsgBox("Learner name " & strIName & " is not in the
system. Do you want to add this Learner?", vbQuestion + vbYesNo, "New
Learner?")

If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frmNewLearner", _
datamode:=acFormAdd, _
windowmode:=acDialog, _
OpenArgs:=strIName

Response = acDataErrAdded

' Set Combo = Forms![frmworkshops]![frmworkshopbookings]![CSID]
' Me.Requery
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End If

Response = acDataErrDisplay

End Sub
Click to expand...
 
Wayne

many thanks spent hours trying to solve ti myself

--
Robert P

Wayne Morgan said:
Try an Else clause in the last If statement and move the last Response
line.
If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frmNewLearner", _
datamode:=acFormAdd, _
windowmode:=acDialog, _
OpenArgs:=strIName

Response = acDataErrAdded

' Set Combo = Forms![frmworkshops]![frmworkshopbookings]![CSID]
' Me.Requery
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Click to expand...
Else
Response = acDataErrDisplay
End If
Click to expand...

As currently written, you're always executing the line "Response =
acDataErrDisplay" and, I suspect, that this would undo your line "Response
= acDataErrAdded".

--
Wayne Morgan
MS Access MVP


Rob P said:
Hi

I am tring to adapt some code found in an MS Access book by John Viescas
must add very good training material

I have a subfrm frmWorkshopbooking and a combo box Combo67. This is
bound to a learner CSID. The combo box displays the learners name but
stores the CSID value

I have input the following code which works well except when returning to
the workshopbooking sub form the combo box still requires to be
requeried. Some attemps made are in as comment lines.

Any help much appricated



Private Sub Combo67_NotInList(NewData As String, Response As Integer)
Dim strIName As String, strLastN As String, strFirstN As String,
intSpace As Integer
Dim intReturn As Integer, varName As Variant
Dim Combo As Control
'pass the input name to the vaiable strIname
strIName = NewData

' check if space in input name
intSpace = InStr(strIName, " ")

If intSpace = 0 Then
MsgBox "enter full name seperted by a space"
Exit Sub

Else: strFirstN = Left(strIName, intSpace - 1)
strLastN = Mid(strIName, intSpace + 2)
End If

intReturn = MsgBox("Learner name " & strIName & " is not in the
system. Do you want to add this Learner?", vbQuestion + vbYesNo, "New
Learner?")

If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frmNewLearner", _
datamode:=acFormAdd, _
windowmode:=acDialog, _
OpenArgs:=strIName

Response = acDataErrAdded

' Set Combo = Forms![frmworkshops]![frmworkshopbookings]![CSID]
' Me.Requery
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End If

Response = acDataErrDisplay

End Sub
Click to expand...
Click to expand...
 
Back
Top