Solved

Access 2013 form with lookup function for updating records

Posted on 2014-09-22
3
2,656 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 34

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now