update a field based on a form completion

[whoknows361]

hello all

I have a form which users enter data based on which client they pick from a combo box.

the combo box(combo_client) is in the header so that when a name is picked the detail section shows the correct record, so that users can enter info for this client.

the combo box(combo_client) uses "Referral_ID" (autonumber) to identify which name is chosen.

The same table(tbl_ReferralData) which holds Referral_ID, also holds a field called "Client_Status".

Currently, the user clicks a name and enters data for htat client, which all works fine.. But how do I, when a user adds the info and clicks the button, make the "Client_Status" field automatically change to the text "open" for the selected client(which is chosen in combo_client)?

Regards and appreciation.

 

[fneily]

On the AfterUpdate event of the button(or did you mean combobox?), put:
Me![Client_Status].Value = "Open

 

[TheAceMan1]

How are ya whoknows361 . . .

To accomplish what you want with your button you need to perform the following:
[ol][li]Validate [blue]all required fields[/blue] have data![/li]
[li]If any field fails validation:
[ol a][li][blue]Prompt[/blue] the user with a message box.[/li]
[li][blue]Set focus[/blue] to the failed field.[/li]
[li][blue]End the button code[/blue] (your back in the form allowing the user to make corrections).[/li][/ol][/li]
[li][blue]Set Client-Status[/blue] if no failures.[/li][/ol]
Special Note: To make this easy ][blue]Client-Status[/blue] should exist in the forms [blue]RecordSource[/blue] so its easily saved! ... (It doesn't have to be on the form ... just in the forms recodrsource!

To check out a simulation, perform the following:
[ol][li]In the [blue]tag property[/blue] of all the fields to be checked, add a question mark [blue]?[/blue].[/li]
[li]In the [blue]On Click[/blue] event of the button, copy/paste the following:

[blue]   Dim ctl As Control, DL As String
   Dim Msg As String, Style As Integer, Title As String
   
   DL = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If Trim(ctl & "") = "" Then
            Msg = "Required Field '" & ctl.Name & "' has No Data!" & DL & _
                  "You'll have to go back and correct this!"
            Style = vbCritical + vbOKOnly
            Title = "Empty Required Field Detected! . . ."
            MsgBox Msg, Style, Title
            Me(ctl.Name).SetFocus
            Exit Sub
         End If
      End If
   Next
   
   Me![Client_Status] = "Open"[/blue]

[/li][/ol]
Give it a whirl and let me know.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[whoknows361]

Thank you both for your help!
And thanks AceMan for field validation code.. just what I needed. One last question for ya in regards to this.. (what you gave me was added and works perfectly).. But this form also has subforms and I would like to add the same field validation with this as well.

So if subform was named Subform_Therapist_Assigned

How would I add this same validation code?

whoknows361

 

[whoknows361]

I figured I would add an additional question which should give me about all of the info I need for form creation from here on out (I hope)...

So I know how to create a form to go to the correct field when a user clicks a name in the combobox (by putting combobox in header).. But I need to know how to use a combobox in the detail section... ie..

2 tbls:
tbl_Referral_Data
ReferralID (PK, auto#)
Client Name
Client DOB
etc.

tbl_Psych_Referral_Submitted
PsychTestingID (PK, auto#)
ReferralID
Psych Name
Psych Address
Etc.

I have a form called Psych_Referral_Submitted. in the detail section is a combobox (Client_Name), which CORRECTLY lists the referral id, client name and client dob. additionally in this form are text fields from tbl_Psych_Referral_Submitted.. which includes the psych's name, address etc.

A button (btn_addPsychInfo) is at the bottom of the form which adds the record to the Psych table.

Currently everything works fine.. the data is added to the psych table correctly, however, ReferralID fieldin the psych table does not get updated with the referralID from the combobox.

I would like to have the referral ID which is associated with teh client name chosen in the combobox to update the field ReferralID in the Psych table..

I would assume I need to add code to the button to ensure this occurs.. Could u let me know how this would be done.

I appreciate all your input.. I have reviewed the help files and documentation with access 07, but I havent found it to be very useful in an applicable sense.

whoknows361