Opening arguments for dialog form 1

[idd]

Hi,

I'm an Access 2000 user

I have almost solved one problem only to find that i'm stuck on a part of it.

Scenario

I have a form [studfinder] which finds students, a combo box is used to find students and when the user types in the combo box the form updates to display the record for the student. If the student doesnt exist then it prompts the user to add the student or retype.

If the user adds the student it opens the [StuAdd] form where the students full details can be added.

I'm using the Not In List event of the combo box. I have managed to get Access to send the newdata through to the form but then when the rest of the details are entered and the [stuadd] form is closed the combo box is not refreshed or requeried this is because I used a

AcDataErrContinue, and opened the form in normal mode rather than dialog, so that I could send the info through to the [stuAdd] form. I found that if I open the form in dialog mode then once the data is entered on the stuadd form it will requery the combo box upon return to the original [studfinder] form.

[the problem]

Opening the form in dialog mode is good as I use the AcDataErrAdded and the combo box is requeried automatically upon return to the orignal form, but I cant find a way to send the newdata through to the stuAdd form.

Anyhelp appreciated.

the code is given below.



------------------------------
start of code
------------------------------


Dim msg, Strfname, StrSurname As String
Dim TwoNames

msg = "Would you like to add " & newdata & " ?"

If MsgBox(msg, vbYesNo) = vbNo Then
SendKeys "{esc}"
Else
DoCmd.openform "FRM_StudDets", acNormal, , , , acDialog, NewData
Response = acDataErrAdded
End If

End Sub


------------------------------
end of code
------------------------------


Any help would be appreciated

Idd

 

[jdegeorge]

I personally avoid using DIALOG. But, Access help says that the MODAL and POPUP features may play a part in it's use:

"The form has a thick (double) border and can include only a title bar, a Close button, and a Control menu. The form can't be maximized, minimized, or resized (the Maximize, Minimize, and Size commands aren't available on the Control menu). You often use this setting for custom dialog boxes. (If you want a form to be modal, however, you must also set its Modal property to Yes. If you want it to be a modal pop-up form, which dialog boxes typically are, you must set both its PopUp and Modal properties to Yes.)"

Maybe you can play with that idea. Hope this helps.

Jim DeGeorge

 

[idd]

JDeGeorge,

Thnanks for looking at my problem, but unfortunately your answer deosnt resolve the problem.

I can open the form in dialog mode through the Docmd.openform command but I want to know how to pass data through to the form being opened, which I think is done through openArgs arguments.

I want to pass the new data which is not recognised by the combo box on the student finder form. Basically I want to be able to tell Access to open the student Add form from the student finder form and also pass the newdata from the combo box to the student add form so that I may add the name programmatically to the relevant fields.

Any help appreciated.

Idd

 

[judgehopkins]

idd, here is a procedure and a function I use on my comboboxes, which, it appears, may help you since you seem to be wanting to do the same thing.

(In your code editor, go to tools/reference and make sure Microsoft DAO 3.6 Object Library is selected; move it close to the top of the list.)

If the user, for example, wants to add the name of a new lender into the combobox, the code checks to see if the lender is already there.

If the user types " aCme" and "Acme" is already listed as a lender, the code will catch that and tell the user to use "Acme".

If, on the other hand, the user types " aCme" and wants to add the name of the new lender--"Acme"--the code will clean that entry up and add it to the table of lenders.

Does this help?

'Not in list procedures for combo boxes 
'below. When you set the Response argument to 
'acDataErrAdded, Microsoft Access enables you 
'to add the value of the NewData argument to 
'the RowSource property setting. The value 
'DataErr will contain the error number for the
'error that just occurred, and Response allows
'you to specify how you want Access to 
'handle the error.
'Response can take the following constants:
'   Response = acDataErrContinue
'This will prevent Access from displaying 
'its own message
'   Response = acDataErrDisplay
'This will cause Access to display 
'its own error message


Private Sub Lender_NotInList(NewData As String, _ 
   Response As Integer)
Dim strNewLender As String
Dim varTemp As Variant
strNewLender = ComboboxCleanUp(NewData)

If DCount("[Lenders]", "Lenders", _
    "[Lenders]='" & strNewLender & "'") > 0 Then
        Me.Undo
        MsgBox "Choose " & _ 
          strNewLender & " from the list.", _
          vbOKOnly, "Use the drop-down list!"
        Response = acDataErrContinue
    Exit Sub

ElseIf MsgBox("Add " & strNewLender _ 
   & " to list?", _
   vbYesNo, "Add a new lender?") = vbYes Then
    Dim db As Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Lenders")
    rst.AddNew
    rst!Lenders = strNewLender
    rst.Update
    Response = acDataErrAdded
    rst.close
    Me.Lender.Value = strNewLender
'the line above refers to the control name 
'on the form, not the table
    Me.Lender.RowSource = Me.Lender.RowSource
'Sometimes the requery does not work 
'so I use the line above for a forced requery
    Response = acDataErrContinue
Else
    Response = acDataErrContinue
    Me.Undo
MsgBox _ 
"Use a lender that is already in the list.", _
vbOKOnly, "A new lender will not be added."
End If

End Sub


Public Function ComboboxCleanUp(ByVal _ 
   strTemp As String)As String
'Used with all comboboxes on control
        strTemp = LTrim(RTrim(strTemp))
'Leading and following spaces stripped
strTemp = StrConv(strTemp, vbProperCase)
'First letter capitalized
ComboboxCleanUp = strTemp
'Cleaned-up string replaces old string

End Function

Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[idd]

Thanks for giving your time to help me JudgeHopkins and JDeGeorge, However, I managed to resolve this problem myself in the manner shown below.

JudgeHopkins,

I didnt get a chance to use the method you mentioned, it was at that time that I found the answer which I am currently using and displayed here for reference. Thank you for your time though. (Oh yeah I have been to your web link and have seen your photo, so you really are a judge, WOW)

I forgot to update the thread to show what answer I used, I was reading through threads I had started and realised this one needed my solution put on it.

This is how I solved the problem.

The code in the notInList Event passes the newdata as newdata to the StuDets form through the openargs of the docmd.openform command. The form is opened up as a Dialog and hence I can use AcDataErrAdded ensuring when the form is closed the combo box is re-queried.


-----------------------------------------------------------------------------
Code in the OnNotInList event of the combo box on the StudFfinder form
-------------------------------------------------------------------------------------

Dim msg As String

msg = NewData & " is not a student in the database," & (Chr(13)) & (Chr(13)) & "Would you like to add " & _
NewData & " as a new student ?" & (Chr(13)) & (Chr(13)) & " Click YES to Add or NO to re-type"

If MsgBox(msg, vbYesNo + vbDefaultButton1 + vbQuestion) = vbNo Then
SendKeys "{esc}"
Else
DoCmd.openform "FRM_StudDets", acNormal, , , , acDialog, NewData
Response = acDataErrAdded
End If

--------------------------------------------------
end of code
--------------------------------------------------




The following code is found in the OnOpen event of the StudDets form, all I did was take the OpenArgs which were passed through to the form and stored them in a variable within the form.

newdata = Me.OpenArgs

and the rest well it all fell in to place, and that was the end of the story.

--------------------------------------------------------------------------
code in the OnOpen event
--------------------------------------------------------------------------

Dim NewData, StrFname, StrSurname, twonames As String
NewData = Me.OpenArgs
If Len(NewData) > 0 Then

twonames = InStr(NewData, " &quot > 0
If twonames = True Then
StrFname = Left([NewData], InStr([NewData], " &quot - 1)
StrSurname = Mid([NewData], InStr([NewData], " &quot + 1)
Else
StrFname = NewData
End If

With Me
.Stu_FName.SetFocus
.Stu_FName = StrConv(StrFname, vbProperCase)
.Stu_Surname.SetFocus
End With
If twonames = True Then
Me.Stu_Surname = StrConv(StrSurname, vbProperCase)
Me.Stu_Add1.SetFocus
End If

End If

-------------------------------------
end of code
-------------------------------------

Hope this all makes sense to anyone who is need.