Message Box Problem

[office_guru]

This is probably so simple but it's really hurting my head trying to figure it out. I have a form with a combo box that will pull up relevant data on the form. I've added a msgbox to the combo box with the intent that if the company doesn't have any records within the table for a message box to appear with the option to add a new record. If the record exist it should ideally come up and no pop-up. I'm guessing I'm missing a condition somewhere? I guess I'd assumed that the combo would have taken care of that, but not the case I'm getting the message box even if a record for that company does exist.

Private Sub ComboCompany_AfterUpdate()
Dim bytResponse As Byte

bytResponse = MsgBox("POCs do not exist for the Company you selected, would you like to create one?", vbYesNo + vbExclamation, "Not In List")

If bytResponse = vbYes Then
Response = acDataErrAdded
stDocName = "frm_pocs_input"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
datefield = NewData
ElseIf bytResponse = vbNo Then
Response = acDataErrContinue 'Show No message
End If

End Sub

As a final note in case it's relevant, the combo box is unbound off of my tbl_company and the remaining bound fields are off of my tbl_POC.

Thanks!

 

[KenHigg]

I suspect there may be better ways to achieve the end result you desire, but as far as using the combo box event - Seems you would need to test before you ever get to any message box stuff. Maybe do a dlookup()?

???
kh

 

[CourteJester]

you need to have another "IF" case in there that determines if the combo box is "null" or not for the company you selected,

If the combo box is null then(not actual code)

Dim bytResponse As Byte

bytResponse = MsgBox("POCs do not exist for the Company you selected, would you like to create one?", vbYesNo + vbExclamation, "Not In List")

If bytResponse = vbYes Then
Response = acDataErrAdded
stDocName = "frm_pocs_input"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
datefield = NewData
ElseIf bytResponse = vbNo Then
Response = acDataErrContinue 'Show No message
End If


From what i am reading you are not checking to see if there will be a POC for the company or not, so that is why the msg box keeps coming up

 

[office_guru]

what is the is null code?

You are correct that I'm missing the is null code. I didn't realize I'd also deleted the code to bring up the record. Can someone help with the correct code please whether it be an if statement or dlookup. Thanks!

Private Sub Company_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Company] = '" & Me![Company] & "'"
Me.Bookmark = rs.Bookmark


NEED SOME SORT OF IF NULL STATEMENT HERE

Dim bytResponse As Byte

bytResponse = MsgBox("This record does not exist, would you like to create it?", vbYesNo + vbExclamation, "Not In List")

If bytResponse = vbYes Then
Response = acDataErrAdded
stDocName = "frm_project_input"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
datefield = NewData
ElseIf bytResponse = vbNo Then
Response = acDataErrContinue 'Show No message
End If
End Sub

 

[KenHigg]

Pseudo code:

if dCount("[*]","myTable", "myCustomeNameFld = " & myForm!myComboBox & "'" = 0 then

   if msgbox("Add new record?") = yes then
         open form with new record
   end if

else

 filter form with myComboBox

end if

???
kh

 

[office_guru]

got it

Thanks for the input.

I actually got it to work using the following:

Private Sub Company_AfterUpdate()
' Find the record that matches the control.
Me.FilterOn = False
Dim rs As Object

Set rs = Me.Recordset.Clone

rs.FindFirst "[Company] = '" & Me![Company] & "'"

If rs.nomatch Then
Dim bytResponse As Byte
bytResponse = MsgBox("This record does not exist, would you like to create it?", vbYesNo + vbExclamation, "Not In List")
If bytResponse = vbYes Then
Response = acDataErrAdded
stDocName = "frm_pocs_input"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
datefield = NewData
ElseIf bytResponse = vbNo Then
Response = acDataErrContinue 'Show No message
End If
Else
Me.Bookmark = rs.Bookmark
End If

End Sub

 

[KenHigg]

Hum... Interesting method. Glad you got it working!

kh