On error handling

[MysticElaine]

Hello,

I have a main Case 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.

If they didn't fill out all 3 fields, then a message states that all three fields are required. They then have to enter the information and try again. Then, if they enter all three fields, if all three are not unique, a message states that the client already exists. They should then be able to look at the listbox that is on the same form and choose the client.

I am having trouble with the error handling. I have tried different ways and nothing works. The closest I came to was getting the message to state client exists but the form closed so they couldn't access the listbox then. Here is my code so far with no error handling.

Private Sub AddNewClient_Click()
If IsNull([First Name]) Or IsNull([Last Name]) Or IsNull(DOB) Then
MsgBox "All fields are required", vbOKOnly, "Error"
End If
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
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
DoCmd.Close acForm, "Client", acSaveYes
End If
End Sub

Any help would be appreciated. Thank you.

[twinnyfo]

MysticElaine,

It looks as if your If ... Then Statements might be a bit out of sync with what you want to do. If none of the Text Boxes are Null (conditions of the First If...Then), there is no need for the second If...Then.

Private Sub AddNewClient_Click() 
On Error Goto EH:
    If IsNull([First Name]) Or IsNull([Last Name]) Or IsNull(DOB) Then 
        MsgBox "All fields are required", vbOKOnly, "Error"
    Else
        Dim ClName As Variant 
        Dim ClientID As Integer 
        ClientID = Me.Client_ID.Value 
        ClName = Me.Client_Name.Value 
        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 
        DoCmd.Close acForm, "Client", acSaveYes 
    End If 
    Exit Sub
EH:
    MsgBox Err.Description
    Exit Sub
End Sub

However, this does not address your desire for the code to look for a pre-existing customer. You could easily do this through either a recordset or through a DLookup function. Without knowing what your Table names are it is difficult to provide detailed assistance.

This may be a start to get you going in the right direction. I'll be glad to provide additional assistance if you need it.

[MysticElaine]

Hi, thank you for the response. Yes, I would love some additional assistance. I am very new to Access and coding so have been teaching myself as I go along. As it stands right now, if a user enters identical information, the Client form closes and populates the Client ID and Client fields of the main Case form. However, a new Client ID is given, which is bad since the client already exists and has a different ID number. This is where I am having trouble. I thought I could add an Error handler, but all that did was give the message that the client already exists and still closed the Client form and populated the Case form. So I thought maybe I needed to move the DoCmd.Close somewhere else, but I didn't know where.

Right now, I have a "Case" form that is linked with the "Cases" table. I also have a "Client" form that is linked with the "Clients" table. On the Case form, they click a New Entry button that pulls up the Client form. The user is supposed to search first for the client and choose from the listbox if they exist (left side of the form) before adding a new client (right side of form). I did make it so that they had to type something in one of the search boxes before being able to add anyone, but that doesn't mean they won't just type one letter and then try to add the client.

For the Client form, I have the "First Name", "Last Name", and "DOB" fields visible and are all indexed to be unique before being added to the Clients table. I also have the "Client ID", which is the primary key, and "Client Name" fields invisible so that when a new unique client is added, those fields are populated into the Case form's "Client ID" and "Client" fields upon the Client form closing.

Sorry for being so long-winded and thanks for the help.

Edit: Can I do

If DLookup("[First Name]","Clients","[First Name]=Clients.[First Name]Then ")

?

[twinnyfo]

OK. A lot going on! Let's try to take this one step at a time.

First things first: Although it is EXTREMELY rare that two people with the same First and Last Names also have the same DOB, that is kinda immaterial. I would highly recommend that in your Clients Table that there simply be a unique identifier for all clients. After all, someone could be looking for "John Smith, 14 Aug 04" and really mean "John Smyth 14 Aug 04". Try to find another way to identify your clients. Then, when you select a client, it is always based on the ClientID (even though the criteria for looking for the Client is based on name and DOB).

Second, if you want the User to look for a client before they can enter a new client, then build your form that way. You can do this by using unbound text box controls in which the user enters data (even partial data) and the db will look for records that match those criteria. Your users should never "accidentally" create a record if you don't want them to, which is why you are coming up with new client IDs.

I know you are asking a lot, and I am not giving a lot, but I promise you, if we work through this piece by piece, we will eventually get there.

What is the first step you want to tackle, because right now, it seems like there is too much for one post.

I'm here to hepp!

:-)

[MysticElaine]

I do have a client ID that is the primary key and is auto number. Also, the only information we can put down to identify the client is first, last, and dob (MM/DD only), which is why all 3 fields must be unique before being added to the clients table.

I already have the form able to type in different text boxes and filter a list box to identify an existing client and then, once chosen, it populates the main form with the info.

My problem is the scenario in which a lazy person decides not to first search for the client and go straight to adding them. They type one letter in the search text box to now enable the text boxes that allow a new client to be added. They then type in the first, last, and dob. They click Add New button and the main form populates with the information. The problem is, even though the main form populates, it populates with the wrong ID number for that client, who already has a unique ID. So I need to be able to let them know that the user already exists and have the client form stay open so that they can then search the list box for the client.

Thanks

P.S. Is there a way I can attach my project here?

Edit: Here is something else I was trying with the recordset but it errors

Private Sub AddNewClient_Click()
If IsNull([First Name]) Or IsNull([Last Name]) Or IsNull(DOB) Then
MsgBox "All fields are required", vbOKOnly, "Error"
Else
    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim str As String
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("Clients", dbOpenDynaset)
    rst.FindFirst "[First Name] = Me.[First Name] AND [Last Name] = Me.[Last Name] AND [DOB] = Me.[DOB]"
    If Not rst.NoMatch Then
    MsgBox "Client exists"
    GoTo Cleanup:
    Else
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
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
DoCmd.Close acForm, "Client", acSaveYes
End If
End If
End If
Cleanup:
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
 
 

It says for the rst.FindFirst that the Microsoft Access database engine does not recognize 'Me.First Name' as a valid field name or expression

[twinnyfo]

Elaine,

Let's go one step at a time. First, the Client entry form. First, make this a completely unbound form--not attached or associated with ANY tables or queries. When the User types in a Name, there will be no Client ID generated, because it is not a record--it's just data.

When the user finishes adding the Last, First and DOB, they click "Add" or "Search"--either one should work for this scenario. The code behind the button will do the following:

1. Search the Current Records for the Client
2. If the Client Exists, go to that record on the Main Form
3. If the Client does not exist, create a new record

Let's start with this functionality first, because I think this is at the heart of your orignal post. Once we get that working, we can address some of the other issues.

BTW, it is possible to post your projec there, but you have to zip it. However, at my work PC, I cannot access it, so I have to "fly blind" unlike many of hte other users here.

[MysticElaine]

I originally had the form have 3 text boxes. As they typed, the listbox containing all the clients would filter and if the client didn't show up, then when they hit the button, the client would be added. However, my supervisor didn't like that idea and thought it was too complicated for people to understand. So he requested that the form have two sides. On the left side, were the search boxes that filtered the listbox. The user selects the client in the listbox if it exists and hits a button that populates the main form. The right side contains the textboxes that allow the user to add a new client, which not only updates the table with a new record, but also populates the main form with the information.

Unfortunately, I can't change any of the forms layouts anymore as someone else is now taking the project to beautify the forms and create all the necessary queries and reports functions for all the different requests that might come up. We have to have it presentable by next Friday. I told them I would work on the On Errors, which I didn't think there would be many to do.

I have attached my project also. I realize you can't access it at work, but unfortuately, I think it needs to be seen to understand what I am doing. I will also paste my coding for all three forms, in case that will help, but I realize that is a lot to look at. Thanks again.

.................................................. .................................................. ..............
*****Read below for coding of each form if not looking at the attachment*****************

Main Form "Case":

Option Compare Database
Option Explicit
 
Private Sub Client_GotFocus()
DoCmd.OpenForm "Client", acNormal, , , acFormAdd
End Sub
 
 
Private Sub Cost_AfterUpdate()
If [Cost] > 0 Then
    [Providers].Enabled = False
Else
    [Providers].Enabled = True
End If
End Sub
 
Private Sub Form_Load()
    [Case ID].Enabled = False
    [Client ID].Enabled = False
    [Client].Enabled = False
    [Date Requested].Enabled = False
    [Staff Requesting].Enabled = False
    [Language Requested].Enabled = False
    [Service Requested].Enabled = False
    [Service Date].Enabled = False
    [Outcome].Enabled = False
    [Date Provided].Enabled = False
    [Providers].Enabled = False
    [Number of Pages].Enabled = False
    [Time].Enabled = False
    [Cost].Enabled = False
    [Comments].Enabled = False
End Sub
 
Private Sub New_Entry_Click()
DoCmd.OpenForm "Client", acNormal, , , acFormAdd
End Sub
 
Private Sub Outcome_Change()
If [Outcome] = "Pending" Then
    [Date Provided].Enabled = False
    [Date Provided] = Null
    [Providers].Enabled = False
    [Providers] = Null
    [Number of Pages].Enabled = False
    [Number of Pages] = Null
    [Time].Enabled = False
    [Time] = Null
    [Cost].Enabled = False
    [Cost] = Null
    [Comments].Enabled = True
Else
        If [Outcome] = "Interpretation" Then
            [Date Provided].Enabled = True
            [Providers].Enabled = True
            [Number of Pages].Enabled = False
            [Number of Pages] = Null
            [Time].Enabled = True
            [Comments].Enabled = True
        Else
                If [Outcome] = "Translation" Then
                    [Date Provided].Enabled = True
                    [Providers].Enabled = True
                    [Number of Pages].Enabled = True
                    [Time].Enabled = True
                    [Comments].Enabled = True
                Else
                    [Date Provided].Enabled = False
                    [Date Provided] = Null
                    [Providers].Enabled = False
                    [Providers] = Null
                    [Number of Pages].Enabled = False
                    [Number of Pages] = Null
                    [Time].Enabled = False
                    [Time] = Null
                    [Cost].Enabled = False
                    [Cost] = Null
                    [Comments].Enabled = True
                End If
        End If
End If
 
End Sub
 
Private Sub Providers_AfterUpdate()
If [Providers] = "Outsider" Then
    [Cost].Enabled = True
Else
    [Cost].Enabled = False
    [Cost] = Null
End If
End Sub
 
Private Sub Providers_GotFocus()
If [Outcome] = "Translation" Then
Dim TranSQL As String
TranSQL = "SELECT DISTINCT Providers.[Staff Name], Providers.Translation"
TranSQL = TranSQL & " FROM Providers"
TranSQL = TranSQL & " WHERE (((Providers.Translation)=True))"
TranSQL = TranSQL & " ORDER BY Providers.[Staff Name]"
 
Me![Providers].RowSourceType = "Table/Query"
Me![Providers].RowSource = TranSQL
End If
 
If [Outcome] = "Interpretation" Then
Dim IntSQL As String
IntSQL = "SELECT DISTINCT Providers.[Staff Name], Providers.Interpretation"
IntSQL = IntSQL & " FROM Providers"
IntSQL = IntSQL & " WHERE (((Providers.Interpretation)=True))"
IntSQL = IntSQL & " ORDER BY Providers.[Staff Name]"
Me![Providers].RowSourceType = "Table/Query"
Me![Providers].RowSource = IntSQL
End If
End Sub
 
Private Sub Search_Case_Click()
DoCmd.OpenForm "SearchCases", acNormal
End Sub
 

From the main form, they can choose to search an existing case, which opens the "SearchCases" form
SearchCases form:

Option Compare Database
Option Explicit
Dim CaseSearchQry As String
 
Private Sub Form_Load()
CaseSearchQry = "SELECT Cases.[Case ID], Cases.Client, Cases.[Language Requested], Cases.[Service Requested], Cases.[Service Date], Cases.Outcome"
CaseSearchQry = CaseSearchQry & " FROM Cases WHERE (((Cases.Outcome) = 'Pending'))"
CaseSearchQry = CaseSearchQry & " ORDER BY Cases.Client, Cases.[Language Requested], Cases.[Service Requested], Cases.[Service Date]"
Me.CasesList.RowSource = CaseSearchQry
Me.CasesList.Requery
End Sub
 
Private Sub UpdateListboxClient()
CaseSearchQry = "SELECT Cases.[Case ID], Cases.Client, Cases.[Language Requested], Cases.[Service Requested], Cases.[Service Date], Cases.Outcome FROM Cases"
CaseSearchQry = CaseSearchQry & " WHERE (Cases.Outcome = 'Pending'"
CaseSearchQry = CaseSearchQry & " AND Cases.Client Like " & Chr(34) & "*" & Me.SearchName.Text & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Language Requested] Like " & Chr(34) & Me.SearchLanguage & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Service Requested] Like " & Chr(34) & Me.SearchService & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Service Date] Like " & Chr(34) & Me.SearchDate & "*" & Chr(34) & ")"
CaseSearchQry = CaseSearchQry & " ORDER BY Cases.Client, Cases.[Language Requested], Cases.[Service Requested], Cases.[Service Date]"
Me.CasesList.RowSource = CaseSearchQry
Me.CasesList.Requery
End Sub
 
Private Sub UpdateListboxLanguage()
CaseSearchQry = "SELECT Cases.[Case ID], Cases.Client, Cases.[Language Requested], Cases.[Service Requested], Cases.[Service Date], Cases.Outcome FROM Cases"
CaseSearchQry = CaseSearchQry & " WHERE (Cases.Outcome = 'Pending'"
CaseSearchQry = CaseSearchQry & " AND Cases.Client Like " & Chr(34) & "*" & Me.SearchName & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Language Requested] Like " & Chr(34) & Me.SearchLanguage.Text & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Service Requested] Like " & Chr(34) & Me.SearchService & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Service Date] Like " & Chr(34) & Me.SearchDate & "*" & Chr(34) & ")"
CaseSearchQry = CaseSearchQry & " ORDER BY Cases.Client, Cases.[Language Requested], Cases.[Service Requested], Cases.[Service Date]"
Me.CasesList.RowSource = CaseSearchQry
Me.CasesList.Requery
End Sub
 
Private Sub UpdateListboxService()
CaseSearchQry = "SELECT Cases.[Case ID], Cases.Client, Cases.[Language Requested], Cases.[Service Requested], Cases.[Service Date], Cases.Outcome FROM Cases"
CaseSearchQry = CaseSearchQry & " WHERE (Cases.Outcome = 'Pending'"
CaseSearchQry = CaseSearchQry & " AND Cases.Client Like " & Chr(34) & "*" & Me.SearchName & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Language Requested] Like " & Chr(34) & Me.SearchLanguage & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Service Requested] Like " & Chr(34) & Me.SearchService.Text & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Service Date] Like " & Chr(34) & Me.SearchDate & "*" & Chr(34) & ")"
CaseSearchQry = CaseSearchQry & " ORDER BY Cases.Client, Cases.[Language Requested], Cases.[Service Requested], Cases.[Service Date]"
Me.CasesList.RowSource = CaseSearchQry
Me.CasesList.Requery
End Sub
 
Private Sub UpdateListboxDate()
CaseSearchQry = "SELECT Cases.[Case ID], Cases.Client, Cases.[Language Requested], Cases.[Service Requested], Cases.[Service Date], Cases.Outcome FROM Cases"
CaseSearchQry = CaseSearchQry & " WHERE (Cases.Outcome = 'Pending'"
CaseSearchQry = CaseSearchQry & " AND Cases.Client Like " & Chr(34) & "*" & Me.SearchName & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Language Requested] Like " & Chr(34) & Me.SearchLanguage & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Service Requested] Like " & Chr(34) & Me.SearchService & "*" & Chr(34)
CaseSearchQry = CaseSearchQry & " AND Cases.[Service Date] Like " & Chr(34) & Me.SearchDate.Text & "*" & Chr(34) & ")"
CaseSearchQry = CaseSearchQry & " ORDER BY Cases.Client, Cases.[Language Requested], Cases.[Service Requested], Cases.[Service Date]"
Me.CasesList.RowSource = CaseSearchQry
Me.CasesList.Requery
End Sub
 
Private Sub SearchDate_Change()
Call UpdateListboxDate
End Sub
 
Private Sub SearchLanguage_Change()
Call UpdateListboxLanguage
End Sub
 
Private Sub SearchName_Change()
Call UpdateListboxClient
End Sub
 
Private Sub EditCase_Click()
DoCmd.OpenForm "Case", acNormal, , "[Case ID]=" & CasesList, acFormEdit, acNormal
DoCmd.Close acForm, "SearchCases", acSaveNo
Forms![Case]![Date Requested].Enabled = False
Forms![Case]![Staff Requesting].Enabled = False
Forms![Case]![Language Requested].Enabled = False
Forms![Case]![Service Requested].Enabled = False
Forms![Case]![Service Date].Enabled = False
Forms![Case]![Outcome].Enabled = True
Forms![Case]![Comments].Enabled = True
End Sub
 
Private Sub SearchService_Change()
Call UpdateListboxService
End Sub
 

From the main form, they can also choose to add a new entry. This opens the "Client" form so that they can either choose an existing client or add a new one.
Client form:

Option Compare Database
Option Explicit
Dim ClientSearchQry As String
 
Private Sub UpdateListboxFN()
ClientSearchQry = "SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.DOB, Clients.[Client Name]"
ClientSearchQry = ClientSearchQry & " FROM Clients"
ClientSearchQry = ClientSearchQry & " WHERE (Clients.[First Name] Like '" & Me.[Search First Name].Text & "*' AND Clients.[Last Name] Like " & Chr(34) & Me.[Search Last Name] & "*" & Chr(34) & ")"
ClientSearchQry = ClientSearchQry & " ORDER BY Clients.[First Name], Clients.[Last Name], Clients.[DOB]"
Me.ClientList.RowSource = ClientSearchQry
Me.ClientList.Requery
End Sub
Private Sub UpdateListboxLN()
ClientSearchQry = "SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.DOB, Clients.[Client Name]"
ClientSearchQry = ClientSearchQry & " FROM Clients"
ClientSearchQry = ClientSearchQry & " WHERE (Clients.[First Name] Like '" & Me.[Search First Name] & "*' AND Clients.[Last Name] Like " & Chr(34) & Me.[Search Last Name].Text & "*" & Chr(34) & ")"
ClientSearchQry = ClientSearchQry & " ORDER BY Clients.[First Name], Clients.[Last Name], Clients.[DOB]"
Me.ClientList.RowSource = ClientSearchQry
Me.ClientList.Requery
End Sub
 
Private Sub AddNewClient_Click()
 
If IsNull([First Name]) Or IsNull([Last Name]) Or IsNull(DOB) Then
MsgBox "All fields are required", vbOKOnly, "Error"
Else
    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim str As String
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("Clients", dbOpenDynaset)
    rst.FindFirst "[First Name] = Me.[First Name] AND [Last Name] = Me.[Last Name] AND [DOB] = Me.[DOB]"
    If Not rst.NoMatch Then
    MsgBox "Client exists"
    GoTo Cleanup:
    Else
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
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
DoCmd.Close acForm, "Client", acSaveYes
End If
End If
End If
Cleanup:
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
 
 
Private Sub Form_Load()
ClientSearchQry = "SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.DOB, Clients.[Client Name]"
ClientSearchQry = ClientSearchQry & " FROM Clients ORDER BY Clients.[First Name], Clients.[Last Name], Clients.[DOB]"
Me.ClientList.RowSource = ClientSearchQry
Me.ClientList.Requery
 
[First Name].Enabled = False
[Last Name].Enabled = False
[DOB].Enabled = False
End Sub
 
Private Sub Search_First_Name_Change()
Call UpdateListboxFN
 
If [Search First Name] = "" Then
[First Name].Enabled = False
[Last Name].Enabled = False
[DOB].Enabled = False
Else
[First Name].Enabled = True
[Last Name].Enabled = True
[DOB].Enabled = True
End If
 
End Sub
 
Private Sub Search_Last_Name_Change()
Call UpdateListboxLN
 
If [Search Last Name] = "" Then
[First Name].Enabled = False
[Last Name].Enabled = False
[DOB].Enabled = False
Else
[First Name].Enabled = True
[Last Name].Enabled = True
[DOB].Enabled = True
End If
 
End Sub
 
Private Sub UseExistingClient_Click()
Dim ClListID As Integer
Dim CLN As Variant
ClListID = Me.ClientList.Column(0)
CLN = Me.ClientList.Column(4)
DoCmd.Close acForm, "Client", acSaveNo
Forms![Case]![Client ID].Value = ClListID
Forms![Case]![Client].Value = CLN
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 Sub
 

[twinnyfo]

Here is a little hepp:

Line 32 in the Clients Form above:

rst.FindFirst "[First Name] = '" & Me.[First Name] & _
    "' AND [Last Name] = '" & Me.[Last Name] & _
    "' AND [DOB] = #" & Me.[DOB] & "#"

I also always recommend keeping your lines of code to less than 80 characters if possible by using the Line continuation marks "& _", as this keeps your code nice and tidy. This also makes creating long string like you have in your Cases and SearchCases Forms. Both methods work, but this saves you some typing.

Are you receiveing erros elsewhere?

Also, the title of this posting is "On Error Handling" and I have noticed that you have no error handling included in any of your code. Are you talking about "errors" or are you talking about preventing a user from doing something they ought not to do?

I also think your number one challenge is that you say this: "Unfortunately, I can't change any of the forms layouts anymore as someone else is now taking the project to beautify the forms and create all the necessary queries and reports functions for all the different requests that might come up." If you can't change the form layouts, then you can't control how the forms get used. In my databases, I shut down everything that I possibly can, so the user can only do what they are supposed to do. If someone doesn't want to follow the rules and look for a pre-existing client first, and we don't shackle them into doing so (via code or forms or whatever), they are not going to do it.

I hope my little bit of code hepped a little bit...

[MysticElaine]

Thanks for the information about the line continuation marks and the use of the # for the date, I forgot about that.

In regards to the On Error tile, in my first post, I had put the code that I wanted the On Error to work on, but I didn't know where to put it as all my attempts had failed. I had gotten the message box in the handler to work, but the form would still close instead of staying open so the user could fix the problem. Right now, Access just automatically closes the form and states it couldn't be saved as it already exists. I wanted to prevent the user from having to start all over by just creating the On Error to trap the error, keep the form open, and let the user choose the client from the listbox instead.

I just read this morning about CancelEvent, so I might try that to keep the form from closing.

Thanks for the info!

[MysticElaine]

For example, this is what I have for the On Error, but the form still closes:

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
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
DoCmd.Close acForm, "Client", acSaveYes
End If
AddNewErr:
If IsNull([First Name]) Or IsNull([Last Name]) Or IsNull(DOB) Then
MsgBox "All fields are required", vbOKOnly, "Error"
Else
MsgBox "Client Exists", vbOKOnly, "Error"
End If
DoCmd.CancelEvent
End Sub

[Luk3r]

This is really a shot in the dark, and I hate to interrupt the conversation this far.. but when you trap errors, don't you have to Resume Next when an error is trapped? So in your AddNewErr: section, where you're actually handling the error, you also need "Resume Next" at the end so that the program knows to keep going after trapping an error.

Edit**: I don't know much about VB4/5/6, so this is really a question for learning on my part in the instance that I'm wrong. I would "Resume" in VB.NET and that's why I brought it up here. Always willing to learn! :)

[twinnyfo]

The "Error" code above will never execute unless there is a "code error".

I believe what you are trying to do is force the user to add a new client if the client is not found in the Clients Table. This is not an "Error"--this is a user not doing what you want them to.

To modify my original response, let's take a look at this:

Private Sub AddNewClient_Click()  
On Error Goto EH:
    Dim ClName As String
    Dim ClientID As Integer
 
    If IsNull([First Name]) Or IsNull([Last Name]) Or IsNull(DOB) Then
        MsgBox "All fields are required", vbOKOnly, "Error"
    Else
        If Nz(DLookup("[ClientID]", "Clients", _
            "[First Name] = '" & Me("First Name") & "'" & _
            " AND [Last Name] = '" & Me("Last Name") & "'" & _
            " AND [DOB] = #" & Me.DOB & "#"), 0) = 0 Then
            'Add the New Client
            ClientID = Me.Client_ID
            ClName = Me.Client_Name
            With Forms![Case]
                .[Client ID] = ClientID
                .[Client] = ClName
                .[Date Requested].Enabled = True
                .[Staff Requesting].Enabled = True
                .[Language Requested].Enabled = True
                .[Service Requested].Enabled = True
                .[Service Date].Enabled = True
                .[Outcome].Enabled = True
                .[Date Requested].SetFocus
            End With
            DoCmd.Close acForm, "Client"
        Else
            'Add Code here to go to the Pre-existing Client
        End if
    End If  
    Exit Sub 
EH: 
    MsgBox Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator" 
    Exit Sub 
End Sub

In line 6 above, it checks to see if all fields were entered. If not, the user is told so and the code stops.

In lines 9-12, (since all fields were entered) the code looks to see if the client can be found int he table. The Nz function is there to handle a Null--which means there was no record).

If the value is 0, which is what Nz will return with no record, then the client is added to the Case Form (lines 14-26)

Line 27 closes the Client Form

Line 29, you would have to build... We can help, but I don't know how your forms/tables look to do this adequately. I need try some things and we will work out solutions if you come across problems.

You will also notice in Lines 2, 33-35, I have true "error handling" code.

Please let me know how this code helps out with the tasak at hand and we can move forward with any of your other challenges with this project.

[twinnyfo]

Luk3r,

In response to your question, resuming is not necessary. For example if you have code that causes an error, but the continuing code is not dependent upon the previous code, then you may want to Resume Next. However, if your following code is dependent upon the previous, you will want to exit the sub.

Here is an example. Let's say you have several picture objects on your form and the code loads a picture from a file and these file names are all hard coded into the VBA. During the execution, the VBA can't find one of the file names, so this causes an error. Just because one file is missing doesn't mean all the files are missing, so you would want to code to continue.

Another example. Let's say that you are trying to find the value of Sales for the Month of May. Based on that value, you need to calculate thirty other values. If your code causes an error determining the value of Sales in the Month of May, since that variable will be unusable, you want the code to exit.

In either example above, it is possible for the VBA coder to determine what the error is and inform the user exactly what went wrong, so it can be fixed (e.g. tell the user the file name could not be found or that it couldn't find any sales for the month of May)

From a training and learning perspective, here is my advice: Whenever you have your code looking for or calculating a value for a variable or "something" that you are going to use, determine what you are going to do if there is a Null value (the Nz() Function above). If you do a DLookup and it does not find anything that meets the criteria, you will have a Null, which is sometimes difficult to use. However, a zero is easy to use. So, if you look for a zero (Line 12 above) and don't find it, then you have a valid DLookup value.

The point here is simple: I put error handling into all my subs and they very seldom fire during execution, because I try to cover as many possibilities as I can with the Code itself. If I get invlaid data, then I handle it without any real "errors". The code will work around it or tell the User they they entered invalid data.

However, the DBA MUST include Erorr handling, because if not, when ther eis a code error, the DB freezes or closes or does really bad things. Your job, as a coder, is to prevent this from happening!

If you see from my post #12 above, it really does seem like the language of "error handling" has two meanings here. It appears the OP wants to redirect users when they do something incorrectly. Error handling in the code tells the code what to do when the code does something wrong.

I hope this hepped to explain things a bit better for you.

[Luk3r]

That was a fantastic explanation and I thank you. Reading scenarios the way you wrote them makes much more sense now :)

[MysticElaine]

Hi, thanks. In theory, that is what I want to do. However, it is having the same issues as my original code in that it isn't seeing that the person already exists in the table. It still populated the "Case" form with a new Client ID, not the Client ID the person already has.

I also don't know what you mean by true "error handling". If all three fields are not entered, it produces the error code 3314 (in which I wanted a msgbox to say "all fields required"), and if all three fields are entered but the person already exists in the table, it produces the code 3022 (which I wanted a msgbox to say "client exists" and the form to not close). At one point in time I tried a Select Case, but it didn't seem to work, though I could have done it wrong.

Thanks.

Edit: I just saw your post to Luk3r and saw how you described "Error Handling"

[twinnyfo]

Well, it appears that Lines 9-26 are trying to add a new client, even if there is an exisiting client.

So, from a trouble shooter's perspective, we have to figure out why it it doing that. Let's add a few lines of code to help us out here (you can always delete this code later on).

Add this code between lines 8 and 9:

        Dim strFirstTest As String
        Dim strLastTest As String
        Dim dtDOBTest as Date
        strFirstTest = Me("First Name")
        strLastTest = Me("Last Name")
        dtDOBTest = Me.DOB
        MsgBox "Here are your values:" & vbCrLf & vbCrLf & _
            "First:  " & strFirstTest & vbCrLf & _
            "Last:  " & strLastTest & vbCrLf & _
            "DOB:  " & dtDOBTest, vbOkOnly
        Exit Sub

Now, when you run the code, what the the pop up tell you the values are and, more importantly, how do they differ from the values input into the form?

I am causing the code to exit before any records are added, because we just want to test the values the code comes up with.

Yes, we are taking this truly at one step at a time.

[MysticElaine]

Ok, I did that and the message box popped up with all the correct values with the exception that the DOB added a year. My table has it as a text value since we can only type MM/DD.

[MysticElaine]

So I took out the # and replaced with ' in the code and it works!!!

*happy dance*

[MysticElaine]

Thank you so much!!!!! I appreciate your time and patience with me and for getting me through this problem!

*Gives you lots of baked goodies*

[twinnyfo]

I am so glad that I could help. I know it seemed to take a long time, but at least now you can move forward!

You know, I am especially fond of chocolate chip cookies! ;-)