Populate Form For An Existing Record

[devGarfield]

Hello,

I have 1 main form, which can be used to edit/create new users. When adding a new record, I want to check if a record existing, if so, then populate the form/subforms and go into edit mode.

To check if the record exists I use an index based on the Lastname and Firstname. This check is done in the After_Update event of the Lastname textbox.

Any help would be appreciated.

Thanks

 

[scriverb]

You can use the DLookUp function to check for the exisitance of a record:

EXAMPLE:

DLookup("[LastName]", "[i]yourtablename[/i]", "[LastName] = '" & FORMS![formname]![LastName] & "' and [FirstName] ='" & FORMS![formname]![FirstName] & "'")

After updating the table name and making sure the form controls are named correctly this function call will return the LastName value only if a match is found on both LastName and FirstName. Although there could be two Joe Smith's. Not a foolproof method.

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[devGarfield]

Hi Bob,

I tried your suggestion, thanks, it works in identifying that the last name exists, but I need to go a step further and populate the form with the details of the record found and change the form into edit mode.

I would be gratful for any help.

Garfield

 

[scriverb]

Sorry about that. I missed the edit part. It does identify that the record exists on both LastName and FirstName. It only returns the LastName value.

But, before we continue why are you wanting to use an Unbound form to perform the adding, editing of data in your table. Why not create a bound form and use the ACCESS wizard to create a combobox that will list all the records currently in the table and let you pick one for editing. After the pick the form will auto populate the fields. Also, a button can be added to trigger the Add New Record process.

Any problem with doing it this way? Please post back and I can help with that.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[devGarfield]

Hi Bob,

I'm pushing the envelop as usual.

The form is a bound form, I already have a combox with all the users by lastname, firstname. Which if they select and entry from will display the correct record.

It's the user's request that if they enter in the first and last names into the form, that it should display the details if present, otherwisw continue to add the record. I would prefer them to use the combo box, but they want this as well.

I hope this makes it a bit more clearer.

Thanks
Garfield

 

[scriverb]

There are better ways to do this but we can set you up with what they are asking. Put this code with the necessary updates in the AfterUpdate event procedure of the FirstName field:

If Not IsNull(Me![LastName]) then
   If IsNull(DLookup("[LastName]", "yourtablename", "[LastName] = '" & FORMS![formname]![LastName] & "' and [FirstName] ='" & FORMS![formname]![FirstName] & "'")) then
      '[i]add record code[/i]
      Me![[i]next_enterablecontrol[/i]].setfocus
   else
       Me.RecordsetClone.FindFirst "[LastName] = '" & Me![LastName] & "' and [FirstName] = '" & Me![FirstName] & "'" 
       Me.Bookmark = Me.RecordsetClone.Bookmark
       Me![[i]next_enterablecontrol[/i]].Setfocus
   Else
       Me![LastName].SetFocus
   End If
End If

You may need to copy this code and modify it slightly and put it also in the Afterupdate of LastName. The initial IF statement is checking to see if the other control has been filled it already. In LastName you have to check FirstName and in FirstName you have to check LastName. The final .setfocus would have to be updated with Firstname also.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[devGarfield]

Thanks for that Bob,

I've added that code, but I'me getting these errors

Run-time error 3201 - You cannot add or change a record bcause a related record is required.

I have some combobox's which are linked to table could this be the cause of this problem. But since all the data already exists in the record found I don't see why this should prevent me from displaying the data.

Could this have something to do with the fact that I'm moving from a form opened in Add mde to Edit mode?

Garfield

 

[scriverb]

If it is erring out on the Add record mode then yes it probably is because we need to cancel that event. You can put a Event.Cancel command just after the first else command. I think this is where we are changing from Add record over to Edit. We need to get off of the new record and then perform the code to find the record and refresh the form with the existing record.

Give that a try and let's see how that works.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[devGarfield]

Hi,

Well I added Docmd.CancelEvent, but nothing happened. I think what's needed is an explicit change of form mode, from Add to Edit.

Garfield

 

[scriverb]

Let's make these changes:

Dim vLName as String, vFName as String
If Not IsNull(Me![LastName]) then
   If IsNull(DLookup("[LastName]", "yourtablename", "[LastName] = '" & FORMS![formname]![LastName] & "' and [FirstName] ='" & FORMS![formname]![FirstName] & "'")) then
      'add record code
      Me![next_enterablecontrol].setfocus
   else
       vLName = Me.[LastName]
       vFName = Me.[FirstName]
       DoCmd.SetWarnings False
       DoCmd.RunCommand acCmdDeleteRecord
       DoCmd.SetWarnings True
       Me.RecordsetClone.FindFirst "[LastName] = '" & vLName & "' and [FirstName] = '" & vFName & "'"
       Me.Bookmark = Me.RecordsetClone.Bookmark
       Me![next_enterablecontrol].Setfocus
   Else
       Me![LastName].SetFocus
   End If
End If

I think this should work for you. Let me know how this works. I have it working here.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[devGarfield]

Hi Bob,

Tried that, didn't work either. Now I'm getting the 3426 error - The action was cancelled by an associated object.

Maybe I should force them to use the combox to check it a user is in the system, instead of this route.

But if you have any more suggestions I'll be willing to try them out.

 

[scriverb]

No we can get this to work. What line of code is it stopping on? I have it working just fine here. Now I took out the Cancel command remember. Copy what I gave you and update the fields, tables, and controls where necessary. I works really great here so we just need to tweak yours a bit.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[devGarfield]

Hi Bob,

Sorry for the delay in getting back. I had a thought about the errors I was getting. They were all releted to fields in the User Table that where combo boxes. So before assigning the recordsetclone I set these values up. as follows -

    Dim rstUser As DAO.Recordset
    Set rstUser = CurrentDb.OpenRecordset("Users")

    rstUser.Index = "UserName"
    rstUser.Seek "=", Me.LastName, Me.FirstName

    If Not rstUser.EOF Then

        MsgBox FirstName & " " & LastName & " Already in the system"
        Me.RecordsetClone.FindFirst "[UserID] = " & rstUser!UserID
        If Not Me.RecordsetClone.EOF Then
            [COLOR=red]Me.cboUserTypeID = rstUser!UserTypeID[/color]
            [COLOR=red]Me.TitleID = rstUser!TitleID[/color]
            Me.Bookmark = Me.RecordsetClone.Bookmark
        End If
    End If

So thanks for your help it was greatly appreciated.