Solved

Access 2013 form with lookup function for updating records

Posted on 2014-09-22
3
3,016 Views
Last Modified: 2014-09-25
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!
0
Comment
Question by:mjchevalier
3 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40336941
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
Else
  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 & "*'"

Or

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

Expert Comment

by:jvandel
ID: 40337078
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.
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 250 total points
ID: 40337253
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.

Me.Requery

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.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What if i make webbased alternative for MS Access 5 72
ERROR 3113 MODULEID NOT UPDATABLE 8 22
90 days before current date 12 33
Record Lock on Database will not go away 9 25
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question