Validate Data and Display MsgBox

[bamphoenix]

Need help to figure out how I can get this to work. I know it's probably
very simple and I've come close several times but it's just not good enough.

I have 2 Tables. One is Members and the other is Enrollments. Both have the
common field of MemberID. I have a form based off the Enrollments table and
need the MemberID to check if the data typed in is not part of the Members
table. If it is not then I would like a Msgbox to appear asking if I would
like to add this Member. If not, then clear. If the MemberID is already in
the Members table then the Member Information fields will auto-populate.
Also I would like the MemberID validating and clearing in the BeforeUpdate
event so that the RecordID will not autopopulate. I have tried to use the
NotInList event and it works great.....however.....the list can not requery
without giving errors.

I have tried this in BeforeUpdate but does not work unless the field is blank
or deleted:

If IsNull(MemberID) Then
MsgBox("Member can not be located. Add it?", vbQuestion + vbOKCancel) = vbOK
Then
DoCmd.OpenForm "Members", acNormal
DoCmd.GoToRecord , , acNewRec
Else
Me!MemberID.Undo

This is what I have for the autopopulate VBA in the AfterUpdate function that
works perfectly!!!:

Private Sub MemberID_AfterUpdate()

If IsNull(MemberID) Then
Exit Sub

End If

Dim dbGA_Medicare_Sales As Database
Dim rsMembers As Recordset

Set dbGA_Medicare_Sales = CurrentDb

Set rsMembers = dbGA_Medicare_Sales.OpenRecordset("Select * From Members
Where MemberID='" & MemberID & "'")

With rsMembers
If Not .EOF Then

FirstName = !FirstName
LastName = !LastName
SSN = !SSN
PhoneNumber = !PhoneNumber
ZipCode = !ZipCode
City = !City
State = !State
County = !County

End If
End With

rsMembers.Close
dbGA_Medicare_Sales.Close

End Sub

 

[Jeanette Cunningham]

Hi bamphoenix,
combo boxes in access have an event called Not In List which can be used to
do what you are asking.
Here is a site with an example
http://allenbrowne.com/ser-27.html

If you do a search for not in list, you will find plenty of examples.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[bamphoenix via AccessMonster.com]

I have tried to use the NotInList event and it works great.....however.....
the list can not requery
without giving errors due to the MemberID field being required as well as a
primary field.

Is there anything else I can use? Or can someone advise of the VBA code to
get this to work?

Hi bamphoenix,
combo boxes in access have an event called Not In List which can be used to
do what you are asking.
Here is a site with an example
http://allenbrowne.com/ser-27.html

If you do a search for not in list, you will find plenty of examples.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Need help to figure out how I can get this to work. I know it's probably
very simple and I've come close several times but it's just not good

[quoted text clipped - 68 lines]

End Sub

 

[Jeanette Cunningham]

You can use a combination of Not In List and opening a the form to add the
new record to that table.
Here is some code that I wrote in answer to another question some time ago.
You may be able to adapt it to suit your situation.

-------------------------------------------
Private Sub cboSubcategory_NotInList(NewData As String, _
Response As Integer)
Dim strMsg As String


' Prompt user to verify they wish to add new value.
strMsg = "Value is not in list. Add it?"
If MsgBox(strMsg, vbOKCancel) = vbOK Then
If IsNull(Me.cboCategory) Then
strMsg = "You must choose a Category before you add a new
Subcategory"
MsgBox strMsg, vbInformation
'clear cboSubcategory
Me.cboSubcategory = Null
Else

DoCmd.OpenForm "fdlgSubcategoryAdd", , , , acAdd, acDialog,
Me.cboCategory & "|" & NewData

' Code will wait until "add" form closes - now verify that it
got added!
'debug.Print NewData
If IsNull(DLookup("SubcategoryID", "tblSubcategories",
"[Subcategory] = """ & NewData & """")) Then
' Ooops
MsgBox "You failed to add a Subcategory that matched what
you entered. Please try again.", vbInformation
' Tell Access we handled the error, but cancel the update
Response = acDataErrContinue
Else
' Tell Access new data was added
Response = acDataErrAdded
End If
End If
Else
' Don't want to add what they typed - show standard error message
Response = acDataErrDisplay
End If

End Sub
-------------------------------


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have tried to use the NotInList event and it works great.....however.....
the list can not requery
without giving errors due to the MemberID field being required as well as
a
primary field.

Is there anything else I can use? Or can someone advise of the VBA code
to
get this to work?

Hi bamphoenix,
combo boxes in access have an event called Not In List which can be used
to
do what you are asking.
Here is a site with an example
http://allenbrowne.com/ser-27.html

If you do a search for not in list, you will find plenty of examples.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Need help to figure out how I can get this to work. I know it's
probably
very simple and I've come close several times but it's just not good

[quoted text clipped - 68 lines]

End Sub