Contact Code Issue

[CJ]

Hi groupies:

I am working with Not In List and having some trouble with the order of
operations and my code. I can almost get it to work, but it is awkward and
not efficient.

Problems are:

#1 I can not get the combo box to update without physically running the query.
#2 When frmContacts opens up, it does not find the matching contact ID. It
doesn't show any contacts at all, even though they are in the query.

Private Sub lngContactID_NotInList(NewData As String, Response As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = strMsg & " Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "NEW CONTACT") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblContacts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!strContactFirst = NewData
rs.Update

PROBLEM #1

DoCmd.OpenQuery "qryContacts"
DoCmd.Close acQuery, "qryContacts"

PROBLEM #2
DoCmd.OpenForm "frmContacts", acNormal, , "lngContactID" =
Forms!frmQuotes!lngContactID

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

End Sub

Thanks for the thoughts and brain waves
CJ

 

[Barry Gilbert]

For the first problem, couldn't you requery the combobox?
lngContactID.Requery

For the 2nd problem, it looks like you'resyntax is off a little. Try this:

DoCmd.OpenForm "frmContacts", acNormal, , "lngContactID = " &
Forms!frmQuotes!lngContactID

Also, you should add lines that closes and destroys your recordset object
when you're done:
rs.Close
Set rs=Nothing
Set db=Nothing

HTH,
Barry

 

[CJ]

Hi Barry:

1st Problem is now fixed. Thank you!!

2nd Problem I now get the following happening......

frmContacts does open up, but instead of showing the new contact that I am
adding into my form, it displays the person that was already there. I also
get my error "An error occurred. Please try again."

When I look at the query, my new contact is in place, but they are not there
for the form.

So, if Shrek was the contact in the record, when I try to add Fiona, the
form opens with Shrek showing up. But, Fiona has been added in the query.

My code is now:

strMsg = strMsg & " Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "NEW CONTACT") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblContacts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!strContactFirst = NewData
rs.Update
lngContactID.Requery

DoCmd.OpenForm "frmContacts", acNormal, , "lngContactID = "
& Forms!frmQuotes!lngContactID
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing

Thanks.

 

[Barry Gilbert]

Firstly, let's clarify terms. You're adding a record to a table, not a query.
Tables store data; queries act on/display data.

When you perform the requery on the combobox, it resets the list of data. It
might not be set to the ContactId that you want. Can you put a break on the
Docmd.OpenForm statement and look at the value of lngContactId? Is this the
Id of the new record you inserted (Fiona, if that really is her name)?

Barry

 

[Brian Bastl]

Why not just open the form and pass the new data?

Private Sub lngContactID_NotInList(NewData As String, Response As Integer)

Dim strMsg As String

strMsg = strMsg & " Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "NEW CONTACT") = vbNo Then
Me.Undo
Response = acDataErrContinue
Else
DoCmd.OpenForm "frmContacts",,,, acFormAdd, acDialog, NewData
Response = acDataErrAdded
End If

End Sub

frmContacts Load event:
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.MyContactControl = Me.OpenArgs
End If
End Sub

HTH,
Brian

 

[CJ]

Brilliant, thanks Brian. Works exactly the way I wanted.

I'm fine with the database development, I just can not get the code to work
for me....some kind of mental block!!

Cheers
CJ