code not working?

[Paul Cooke]

Hi guys could someone please point me in the right direction to "fix" this code please. I have tried my best to do it but I don't really know what I'm doing to be honest ..Im just reading other code for tips and hoping it works !!

when I click 'cancel' on the 1st msgbox everything works fine it goes back to the combo. If I click 'ok' the 2nd msg box comes up no problems but when I click 'ok' on that one the 1st msgbox comes up again.

I then click 'ok' on that and the 2nd msgbox comes up again I click 'ok on that and I get a runtime error the close action was canceled.

I keep changing the docmd close line but nothing seems to work

I would be very grateful for any advice or help you can offer

Many thanks

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

intAnswer = MsgBox("The Production or Employer  " & Chr(34) & NewData & Chr(34) & _
"  has not been stored in the database yet. Are you sure the spelling is correct? If incorrect please click Cancel and try again." _
& vbCrLf & "" & vbCrLf & _
"If spelling is ok and you are with the patient now. Please complete a paper treatment form." _
& vbCrLf & "" & vbCrLf & _
"If inputing from a paper treatment form, please stop the entry and contact me.", vbOKOnly + vbOK, "Unknown Production Or Employer Name")
If intAnswer = vbOK Then
    MsgBox "The unknown Production or Employer has not been added to the database.", vbInformation, "Confirmation"
    DoCmd.Close acForm, "TreatmentForm"
    DoCmd.OpenForm "Form1"
    Response = acDataErrContinue
Else
    MsgBox "Please select a valid Production or Employer name from the list.", vbInformation, "New Data Not Added"
    Response = acDataErrContinue
End If
cboProductionEmployerDetailsID_NotInList_Exit:
    Exit Sub
cboProductionEmployerDetailsID_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume cboProductionEmployerDetailsID_NotInList_Exit

 

[Paul Cooke]

I Think I may of worked it out but would someone mind looking at this code for me to see if it is ok please

Many thanks

Private Sub cboProductionEmployerDetailsID_NotInList(NewData As String, Response As Integer)
intAnswer = MsgBox("The Production or Employer  " & Chr(34) & NewData & Chr(34) & _
"  has not been stored in the database yet. Are you sure the spelling is correct? If incorrect please click Cancel and try again." _
& vbCrLf & "" & vbCrLf & _
"If spelling is ok and you are with the patient now. Please complete a paper treatment form." _
& vbCrLf & "" & vbCrLf & _
"If inputing from a paper treatment form, please stop the entry and contact me.", vbOKOnly + vbOK, "Unknown Production Or Employer Name")
If intAnswer = vbOK Then
    MsgBox "The unknown Production or Employer has not been added to the database.", vbInformation, "Confirmation"
    DoCmd.Close FormasacObjectType = acDefault, "TreatmentForm", acSaveNo
    
    DoCmd.OpenForm "Form1"
    Response = acDataErrContinue
Else
    MsgBox "Please select a valid Production or Employer name from the list.", vbInformation, "New Data Not Added"
    Response = acDataErrContinue
End If
cboProductionEmployerDetailsID_NotInList_Exit:
    Exit Sub
cboProductionEmployerDetailsID_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume cboProductionEmployerDetailsID_NotInList_Exit

 

[John Big Booty]

Have you tested this latest code? If so what happens

 

[John Big Booty]

Why are you closing TreatmentForm ?

 

[John Big Booty]

The sample posted here may provide a few pointers for you.

 

[Paul Cooke]

many thanks for the replies John,

The second lot of code i posted does seem to sort of work i.e Form1 opens but the TreatmentForm remains open in the background (Im working on this with accesss Tabs whilst designing the DB so not sure if that is why it stays open or not?)

I want the form to close so that the users have to follow a 'set flow' of data input through various other forms / navigation. If it or other forms are left open I can gurantee they would just select them and enter information without entering other information needed.

To be honest the code is a bit of a quick fix for now as the DB is still in an early design stage what Im hoping for on the control of this code (once other forms ect are done) is if they enter an item not on the list for the DB to automatically email the db administrator to let them know an invalid entry has been attempted

eg if they enter "testproduction" and it is not in the list a msgbox will tell them they cannot add the name for now and the system will email me to say something like "Someone has tried to enter the name "testproduction"....

Is this actually possible though ??

Many thanks

 

[John Big Booty]

If you have a look at the sample I linked to previously you should be able to bash the code I have in the Not In List event to add new items to your Combo box list.

Use the acDialog property of the AcWindowMode of OpenForm method, to open you form with it's Modal and Pop Up properties set to True, this will ensure that your user deals with that form prior to returning to the origin form.

Yes you could certainly have Access send you a message when a new item needed to be added.

 

[Paul Cooke]

Thats great many thanks for the reply and advice John

Paul

 

[John Big Booty]

To send an email you could use something like;

    Dim strMsgTxt As String
    
    strMsgTxt = "A user has attempted to add an Item to the combo list"
    
    DoCmd.[URL="http://msdn.microsoft.com/en-us/library/bb214075%28v=office.12%29.aspx"]SendObject[/URL] acSendNoObject, , , "YourEmailName@YourCompany.com", , , "Attempt to modify list", strMsgTxt, False

To determine the user have a look at this article, you could then insert that code into the above to add the user details.

 

[Paul Cooke]

thanks john - much appriciated

 

[Paul Cooke]

Hi sorry to post on this again but just wanted to check something.

I have got the email bit to work but when a value triggers it I get a pop up in access saying a program is trying to send an email message on your behalf...... and asking if I want to 'Allow' or 'Deny' it.

is there any way of by-passing this through the code or is it possibly the security settings on the pc?

many thanks again

 

[John Big Booty]

There are a couple of solutions here and here.