Auto fill Field

[bill crumpton]

I have a one field text form that has no record source that a user enters a case number. once the case number is entered the user presses enter and another form pops up for the user to enter personal info. If the case number is already in that table then existing info will already be there which is fine. However, what I would like to do to save re entering the case number is that on the first popup form that thenuser enters the case number, if this is the first time the case number is entered to have the case number automatically populate in the second form instead of having the user re type the case number. Any help is greatly appreciated.

 

[Jack Cowley]

I assume that you have some code on the second form that determines if the case number already exists and then proceeds to fill in the rest of the data if it does. If the case number does not exist and the first form remains open then

Me.CaseNumber = [Forms]![FirstFormName]![FieldNameWithCaseNumber]

should do the trick. If the first form is closed then you can pass the CaseNumber to the second form using the OpenArgs argument of the OpenForm method. If you do not know how to use the OpenArgs argument then repost.

 

[bill crumpton]

Jack,
Thanks for the response. I have a criteria in qbe of the record source of the second form coming from the text box of the first form. The first form does close so I guess I will have to pass the text over to the second form. I probably need help on openargs command please. Thanks.


P.S. Will I need to put the code on the default value of the case number field of the second form?

[This message has been edited by bill crumpton (edited 10-20-2001).]

 

[Jack Cowley]

In the OpenForm method use syntax similar to this but replace CaseNumber with the actual number. If have enclosed it in quotes assuming a text value but if it is numeric then leave the quotes off.

DoCmd.OpenForm "FormName", , , , , , "ABC123"

In the On Load event of the second form use code like this:

If Not IsNull(Me.OpenArgs) Then
Me.FieldOnForm = Me.OpenArgs
-- or whatever you want to do with the data
End if

Good luck...

 

[bill crumpton]

Thanks Jack worked great.
One more question please. I want to create a pop up message box to warn the user that a required field is not filled in and the record will not be saved. I have it almost working but, how do I assign the "retry" "ok" buttons on the message box? Thanks again.

 

[SteveA]

If you are calling MSGBOX in code, you can do the following:

Msgbox "My Error Message", vbRetryCancel

This does actually return a value, so you can do checks on it. ie If Msgbox("My Error Message", vbRetryCancel) = vbRetry then ...

HTH
SteveA

 

[Jack Cowley]

Just to amplify on SteveA's answer just a bit.... The OK and Retry buttons cannot go together so SteveA is correct in Retry and Cancel as the choice. Here is one another way to write the code:

Dim Response As Integer

Response = MsgBox("You have not filled in a required field.", vbRetryCancel + vbDefaultButton1)

If Response = vbRetry Then ' (Default)
do something here
Else 'vbCancel
do something else
End If

 

[bill crumpton]

Thnaks Jack and Steve,
I understand now how the buttons work however if I just want the retry button to remove the message box and set the focus to the required field how would that code be written. I am assuming thatthe cancel button would exit the message box without any effect. Correct?

BAC

 

[SteveA]

Its up to you what to do with the return value. All the Msgbox does is return which button the operator pushed. You can decide to do anything you want after the operator has made their selection. ie

Dim intReturn as Integer
intReturn = Msgbox("Hello World",vbRetryCancel)

If intReturn = vbRetry then
[Forms].[MyForm].[MyField].SetFocus
ElseIf intReturn = vbCancel then
DoCmd.Close
Else
Msgbox "Unexpected Result",vbOkOnly
End If

HTH
SteveA

 

[bill crumpton]

Steve,
Thanks that worked great!!!