NotInlist event driving me insane !

[Paul Cooke]

I have tried all day to sort this out myself and learn something but my lack of knowledge and understanding has finally won over hence this post !! So...

(please note 'Form1' is a test form the actual message in the code below is from the control I want to use the code on - when it works!)


I have a form 'Form1' on this form is a combo called Combo1.

If I enter text in the combo box that is not in the list, a MsgBox pops up the message informs the user the data cannot be entered ect ect..

1.They click ok
2.Another message pops up asking if they want to exit the form or enter another record
3. If they select 'No' then Form1 should close without saving anything and 'MainForm' should open
4. If they select 'Yes' the msgbox goes Form1 is on the screen at a new record.

I have tried tons of different ways to do this but really have lost the will to live. Please will someone help me with this code

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

Dim intResponse As Integer
Dim strMsgTxt As String

intResponse = MsgBox("There is no record of the Production / Employer name:-" & vbCrLf & vbCrLf & Chr(34) & NewData & Chr(34) & vbCrLf & vbCrLf & _
"If the patient is with you now, please complete a paper treatment form." & vbCrLf & vbCrLf & _
"If you are entering the details from a paper treatment form, please put form to one side and await further instructions." & _
vbCrLf & vbCrLf & "An email has been sent to Paul to check the Production or Employer details.", vbInformation + vbOKOnly, "Production or Employer Name")
Response = acDataContinue

If MsgBox("Do You wish to enter another treatment record", vbQuestion + vbYesNo, "Option") = vbNo Then
DoCmd.Close
End If

There was other bits of code at the bottom such as GotoRecord ect ect but i have changed it so many times I can't actually remember what I've done.

I will be really really REALLY grateful for some help,

Many thanks

Paul

 

[John Big Booty]

Paul a major problem is that you have two message boxes, and you are dealing with them using different methods, you need to be consistent in how you deal with message boxes and their responses.

Your first message box has no logical test for it's response, whilst you second message box only deals with a No response, there is no provision for dealing with a yes response.

 

[Paul Cooke]

Hi John thanks for the reply - you seem to be my Guru today !! I'm grateful

I'm at the point now of 1, smoking again ! and 2 asking for someone to write the code for me so I can move on with the dB. I really don't like asking for code to be written as I prefer to try and work it out with help from people like yourself or the net - I have actually just ordered an Idiots Guide to VB of amazon !

If you could help me with this I can only offer you my heartfelt thanks but if it is to cheeky of me to ask I totally understand. I appricate my original post was a bit messy especially the code quoted. In theroy I know what I want it to do but just can't put it into practice.

Im at your mercy .. not quite on my knees yet but will beg if I have to !!!

seriously though if you feel me asking this on the forum is too much could you point me in the right direction of where to go to get this written for me.

Many thanks John.

 

[Paul Cooke]

as a footnote the code posted was actually incomplete as I had messed about with it so much and delete lines before giving up !

 

[John Big Booty]

Try something along the lines of;

Dim strMsg1 As String
Dim strMsg2 As String

strMsg1 = "Message text for First MsgBox"
strMsg2 = "Message text for Second MsgBox"

If MsgBox(strMsg1, vbQuestion + vbYesNo, "Option") = vbNo Then
    [COLOR="SeaGreen"] 'Insert code for No response First MsgBox[/COLOR]
Else
     [COLOR="SeaGreen"]'Yes response of First MsgBox follows[/COLOR]
     If MsgBox(strMsg2, vbQuestion + vbYesNo, "Option") = vbNo Then
         [COLOR="SeaGreen"] 'Insert code for No response for Second MsgBox[/COLOR]
     Else
         [COLOR="SeaGreen"] 'Insert code for Yes response for Second MsgBox[/COLOR]
     End If
End If

 

[boblarson]

If you read this:
http://support.microsoft.com/kb/197526

you should see that you have the wrong constant. Plus you should deal with both if they want to add to the list and if they don't (unless you're not allowing adding to the list).

The constant I'm talking about is the one you have shown as:

acDataContinue

But in reality it is:

acDataErrContinue

 

[Paul Cooke]

Many thanks guys for your responses - i sincerely apologise for not replying soon but I have been a bit snowed under with other more mundane day to day work !!

I am looking at the whole structure of the DB again to tidy up what code is there ect and starting afresh doing bit by bit

I will post back the results on the Notinlist once I've finally worked out what it is suppose to be doing

Thanks for your time again !

Paul

 

[gemma-the-husky]

another way of doing it that you may find easier is this

have a button to open the normal/edit form for the table that supports the combo box/list box (or just open the form from your switchboard/menu or any other way).

now have a button on your form, that just says mycombobox.requery (or whatever) - job done

you can even open the edit form, wait for it to close, and then requery the combo all in one - but that needs a function in a module.