Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3902
  • Last Modified:

Access 2013 form with lookup function for updating records

I have been creating a project in Access 2013 and have no prior experience with Access but with help from people like you, things have been working well.  I have a single table, a report and a data entry form which people use to enter records into a simple single table.  Now they need a form to update records.  I have found that creating a form using the wizard works when I change the form properties to NO for Allow Additions and NO for Allow Deletions.  The problem with updating the data using this simple form is that you have to scroll through many records to find the one you need to change.  My question is how can I setup a function to allow the user to select the record he needs to update?  The field to use would be JobTicket.Jobnum.  Can you please help me design this form so the user can pick the record he needs to update using the Jobnum field?  Thank you very much!
2 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Note that in general you don't need a separate form to Editing and Adding records, depending on how you've setup the original form.

You can add Search features to your form. For example, if you want user to search for an Invoice number, you could include a Textbox (perhaps in the Header, not the Detail section) where the user could enter an Invoice number, and then include a Button with code in the Click event like this:

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

rst.FindFirst "Invoice='" & Me.txtInvoice & "'"
If Not rst.NoMatch Then
  Me.Bookmark = rst.Bookmark
  Msgbox "invoice not found"
End If

Basically, this use the RecordsetClone of your Form's Recordset to find the record. If it does, it moves the form to that record, but if not it pops up a MsgBox.

If you want to allow "wildcard" searches, you can do this:

rst.FindFirst "Invoice='" & Me.txtInvoice & "*'"


rst.FindFirst "Invoice='*" & Me.txtInvoice & "*'"
If that seems to complex you could insert a button from the design tab (make sure you have control wizards enabled)
the control wizard will let you choose what you want to do with the button. Choose "Find Record" and follow the prompts.
I prefer to create all my apps as if they might some day need to be upsized to SQL Server.  That means starting out with good client/server techniques.  A simple, almost codeless (one line) solution, is to modify the form's RecordSource query to get its criteria from a combo on the form's header.

Select ... From ... Where SomeField = Forms!myForm!cboSomeField;

Using this technique, the form will always open to an empty record.  This is helpful since it keeps people from just typing into the first record unless what they wanted to do was to add.

Then to find a particular record, the user uses the unbound combo in the form's header.  The AfterUpdate event of the combo has the sum total of one line of code.


I use this method because it reduces the amount of network traffic caused by Access particularly when Access is linked to ODBC data sources where the query will actually run on the server.  The old style "Access" technique of binding forms to tables or to queries with no criteria and then using filters requires that Access request the entire recordset be downloaded from the server and then manipulated locally.

Sometimes, a form will work better with two combos.  One that lists client name and the other that lists the client number.  Either will find the correct record and it gives the user a choice.  The change would be to the query.

Select .. From .. Where SomeField = Forms!myForm!cboSomeField OR SomeField2 = Forms!myForm!cboSomeField2;

Of course, now you need two lines of code because the AfterUpdate events of each combo need to requery the form.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now