form button and error handling

MysticElaine

Registered User.
Local time
Today, 16:36
Joined
May 26, 2014
Messages
24
Hello,

I have a few questions that tie in together. I have a Main form, that when I click a button, a new Client form opens. On the Client form, I have 3 text boxes that users use to create a new client, first name, last name, and date of birth. I also have 2 invisible text boxes named Client name and ID that I ended up creating due to one of the problems I am encountering. All text boxes are bound from the Clients table, and the client name is just the first and last concatenated. I then have an Add New Client button. On the Clients table, the ID is the primary key and the first, last, and dob fields are all required and indexed to all be unique before the client is added to the table.

What I want, is for after the first, last, and dob text boxes are filled out, when I click Add New button, the client ID that was automated from the entry as well as the concatenated client name are populated into the Main form and the Client form closes.

The issue I am having with this is that when I try to use the openform command,
Code:
DoCmd.OpenForm "Case", acNormal, , "[Client ID]=" & [Client ID], , acNormal
it doesn't work. I used this on another form (pulling the info from a listbox) and it worked perfectly, so I don't know exactly what I am doing wrong. If I add Clients. before the [Client ID], it says variable not defined. If I do Me. then the ID and client name don't transfer to the Main form. I then tried to get around whatever the issue was by doing this code
Code:
Private Sub AddNewClient_Click()
If Me.First_Name <> "" And Me.Last_Name <> "" And Me.DOB <> "" Then
Dim ClName As Variant
Dim ClientID As Integer
ClientID = Me.Client_ID.Value
ClName = Me.Client_Name.Value
DoCmd.Close acForm, "Client", acSaveYes
Forms![Case]![Client ID].Value = ClientID
Forms![Case]![Client].Value = ClName
Forms![Case]![Date Requested].Enabled = True
Forms![Case]![Staff Requesting].Enabled = True
Forms![Case]![Language Requested].Enabled = True
Forms![Case]![Service Requested].Enabled = True
Forms![Case]![Service Date].Enabled = True
Forms![Case]![Outcome].Enabled = True
Forms![Case]![Date Requested].SetFocus
End If
End Sub

This makes sure nothing populates, but it doesn't give an automatic error message until I actually try to close the program, which then states that the missing field needs an input.

I wanted to try to add an On Error. I am wanting the Add New button to msg that all fields are required if they try to add the new client without completing all fields. I also wanted it to add an error message if after filling all three fields and trying to add new client, if all three weren't unique then a msg would state that the client already exists. I didn't know exactly where to put the error though or if I should try to do a select case version. What I tried ended up causing the program to be in a state where I could only click OK but nothing would happen and I had to End Task to shut it all down.

So I guess even though I got around my first issue, I still don't understand why it won't work. I would really like help in figuring out how to do the error handling. Thanks for any help.
 
Last edited:
So I now have this code
Code:
Private Sub AddNewClient_Click()
On Error GoTo AddNewErr
If Me.First_Name <> "" And Me.Last_Name <> "" And Me.DOB <> "" Then
Dim ClName As Variant
Dim ClientID As Integer
ClientID = Me.Client_ID.Value
ClName = Me.Client_Name.Value
DoCmd.Close acForm, "Client", acSaveYes
Forms![Case]![Client ID].Value = ClientID
Forms![Case]![Client].Value = ClName
Forms![Case]![Date Requested].Enabled = True
Forms![Case]![Staff Requesting].Enabled = True
Forms![Case]![Language Requested].Enabled = True
Forms![Case]![Service Requested].Enabled = True
Forms![Case]![Service Date].Enabled = True
Forms![Case]![Outcome].Enabled = True
Forms![Case]![Date Requested].SetFocus
End If
If IsNull([First Name]) Then
MsgBox "First name is required", vbOKOnly, "Error"
End If
If IsNull([Last Name]) Then
MsgBox "Last name is required", vbOKOnly, "Error"
End If
If IsNull(DOB) Then
MsgBox "Date of Birth is required", vbOKOnly, "Error"
End If
ExitAddNew:
Exit Sub
AddNewErr:
MsgBox "Client already exists! Please search for client in the listbox to the left.", vbOKOnly, "Error"
Resume ExitAddNew
End Sub

So now it does the messages if all three fields are not filled out. It also does the message that the client already exists. However, it closes the Client form and then displays the client exists message. Is there a way to keep the form from closing so I can find the client in the listbox that is also on the form?
 
Last edited:

Users who are viewing this thread

Back
Top Bottom