Locate existing record-update it on the form

I placed a command button on a form and want to use it to search for an 
existing record and then populate the form with that record so I can 
update/add to it.
The form's record source is a table (tblRFPTracking).
I'm having a mental block on how to accomplish this and appreciate any help.
Linda
0
Utf
2/10/2010 8:10:15 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
1867 Views

Similar Articles

[PageSpeed] 17

Here's one approach...

Add a combobox (unbound) to the form (I stick mine in the header).

Have that combobox list the records available for editing.  I usually only 
include the bare minimum necessary to distinguish one record from another. 
Use a query to "feed" this combobox.

Use another query that returns all of the records that might go into the 
form, and all of their fields that you'd want to edit.

Bind the form to that (second) query.

Now modify that (second) query to point to the combobox on the form for the 
ID of the record you wish to edit.  Note that this means you need to have 
the first column of the (first) combobox query include the ID.  You can set 
the width of that first column to 0 so you don't have to see it.

One more thing ... in design view for the form, highlight the combobox and 
add an Event Procedure to the AfterUpdate event.  It needs:

    Me.Requery

Here's the gist of how this works:

  *  You open the form, the combobox is empty, so the form/query returns 
that record (i.e., none)
  *  You pick a record from the combobox and <tab>/<enter> out ...
  *  The form reruns the query, which now finds a value in the combobox
  *  The form loads the record with the ID of the record selected in the 
combobox.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"ADB_Seeker" <ADB_Seeker@discussions.microsoft.com> wrote in message 
news:873A16CB-0920-40F6-A2D2-85F0562AA234@microsoft.com...
>I placed a command button on a form and want to use it to search for an
> existing record and then populate the form with that record so I can
> update/add to it.
> The form's record source is a table (tblRFPTracking).
> I'm having a mental block on how to accomplish this and appreciate any 
> help.
> Linda 


0
Jeff
2/10/2010 8:23:55 PM
The command button wizard will do that for you. Use find record.
-- 
Milton Purdy
ACCESS 
State of Arkansas


"ADB_Seeker" wrote:

> I placed a command button on a form and want to use it to search for an 
> existing record and then populate the form with that record so I can 
> update/add to it.
> The form's record source is a table (tblRFPTracking).
> I'm having a mental block on how to accomplish this and appreciate any help.
> Linda
0
Utf
2/10/2010 8:30:05 PM
Thank you! I knew it was something simple.
Linda

"golfinray" wrote:

> The command button wizard will do that for you. Use find record.
> -- 
> Milton Purdy
> ACCESS 
> State of Arkansas
> 
> 
> "ADB_Seeker" wrote:
> 
> > I placed a command button on a form and want to use it to search for an 
> > existing record and then populate the form with that record so I can 
> > update/add to it.
> > The form's record source is a table (tblRFPTracking).
> > I'm having a mental block on how to accomplish this and appreciate any help.
> > Linda
0
Utf
2/10/2010 9:01:03 PM
Reply:

Similar Artilces:

Update attribute using ldp
Hi There, I have written an ldf script which creates few custom attributes and an auxilliary class which contains these custom attributes. Also this auxilliary class is part of Users and OrganizationalUnit classes. Now I want to update the values to the custom attributes. How do I do it using ldp? I can set values for the inbuilt attrbutes without any issues, however when I try to set the values for the custom attributes I get below error, Error 0x20B5 The name reference is invalid. Please advise. Thanks, Suresh -- sureshpalani ----------------------------...

Problem with keyboard input being locked in a form
I have a *very* weird problem (Access 97) that first developed sporadically a couple of days ago while working, and that now happens all the time. The symptom is this: When I try to edit the controls on the form, keyboard input is ignored. It's not the keyboard, because in the Immediate Window I can still type. It's not a record lock, because I can open the table and edit the record without problems. The controls are not locked if I check the .Locked property in the Immediate window. The underlying recordsource is not uneditable because I can change values in the underlying ...

adding a new record to a table from a form (works in edit mode)
I have a form which shows select field from a table (showing all rows of data), and have 2 buttons , one to edit, one to add. The edit button works perfectly DoCmd.OpenForm "frm_TRA_EditRoute", , , "[anRouteID]=" & Me.Text62.Value, acFormEdit, , True the add button does not. DoCmd.OpenForm "frm_TRA_EditRoute", , , , acFormAdd in edit mode, the form loads up with all of the details (the autonumber correct, the combo boxes drawing in the correct data), however in add mode the autonumber box is just blank (doesn't say "(Autonumber)" like it ...

Chart Location
Hi, How to specify the location of a chart in a sheet (The sheet contains 4 charts).The chart location should vary dynamically depending on the values associated with it. Thanks and Regards, Sumathi Sumathi - You need to specify the coordinates and dimensions of the chart's parent chart object, in pixels. With ActiveChart.Parent '' or With ActiveSheet.ChartObjects(1) .Top = 50 .Left = 50 .Height = 175 .Width = 225 End With - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTe...

Tax Detail on blank invoice form (non SOP)
how I can print tax detail on blank invoice form (non SOP blank form) ? On Oct 21, 11:34=A0am, Raheel Merchant <RaheelMerch...@discussions.microsoft.com> wrote: > how I can print tax detail on blank invoice form (non SOP blank form) ? what tax detail you want to have printed? All applicable Tax sepertely i.e GST PST HST <epassapera@gmail.com> wrote in message news:10f0735c-ad62-4619-aee7-330d33b6fd8e@v15g2000hsa.googlegroups.com... On Oct 21, 11:34 am, Raheel Merchant <RaheelMerch...@discussions.microsoft.com> wrote: > how I can print tax detail on blank invoice f...

DPM 2010 RC co-locate SQL Server
I'm creating a protection group for SQL Server 2005 SP3 (CU8) with DPM 2010 RC. I have about 15-20 small databases and about 10-15 larger databases. When I create the protection group the option to co-locate is checked (and not gray), but when the protection group is created I end up with many 10gb disk partitions. Isn't co-location supposed to prevent that? I read that the default for co-location is 30 databases (how do I increase that?) so I tried hand picking the larger databases from the list rather than the auto option and only using those in my protection group....

is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data?
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? i mean xls not xsl "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:eDjijGEeFHA.2584@tk2msftngp13.phx.gbl... > is it possible to execute write to the fields in another .xsl form a macro > in another .xsl? e.g. some way to load another .xsl into an .xsl macro and > write to its data? > > Both workbooks are open? workbooks("book1.xls").worksheets("sheet1")...

Location Location Location? No, Traffic Traffic Traffic! #9
Do you want a great backend product that is selling like crazy this week? My readers are running to get this NEW CONCEPT! I�m selling more this week than I ever did in the past months with any other program. My commissions are climbing very fast. I�m really excited! This is the best ad copy I�ve read in months! It sells. If you haven't felt the shockwave yet, stop everything you are doing and read this to be one of the first promoters. The inner circles of the shockwave are raking in the profits like fury. This will pay us for years to come as it is the first �Wave� of the NEW I...

Macro location
I have a file called PERSONAL.XLS that contains all my macros.It is located in STRARTXLS I had reason to copy that file and an other Excel file to drive A(Floppy) Now every time I try to run a macro the computer looks to drive A. How can I stop this ? Thanks -- Norton Virus checker 2003 says this email is clean First off, you should never copy files from Excel to a floppy - it gives too many problems because of the way Excel uses its temporary file. The file can get corrupted. Save to our hard drive and copy from there. The change of location is "a feature not a bug" <grin>...

blank fields on record lists
My documents like accounts, cases, contacts which are listed on main page have blank field 'priority' and some others fields. I noticed that this problem involve only fields definied as 'pickup list' - either predefined in MS CRM fields (like 'priority') and my customized fields. It's very heavy problem because I can't sort these documents by category 'priority' (example) or by my own fields. Any ideas appreciated. JP ...

Todays Date that does not update
I created a macro and a button that when clicked it entered todays date in a cell, which is what I wanted. I used "Today( )". Problem is the date keeps updating to todays date and I want it to stay the date I entered it in. Is there some other value or formula to do this so it does not update everyday? Thanks!! You need to enter the date itself. Any function will always update to the current date. Ctrl+; will enter the current date in a cell. In VBA, use Range("A1").Value = Date -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC...

Problem Deleting Check Boxes created from Forms Toolbar
I managed to overlay about 200 checkboxes in one cell (long story). I have too much invested in this worksheet to just start over. Deleting the row doesn't help, they just move somewhere else. Other than continuing to delete them one by one, does anyone have a suggestion for clearing this cell? Thanks in advance. Try 'Edit' om the Toolbars, 'Go to' > 'Special' > check 'Objects' . Right click one of the highlighted objects and Cut and it will Cut them all, clear the clipboard and that should be that, Regards, Alan. "Suzan" <Suzan@d...

How to avoid table locks when deleting\ inserting huge records
We have a scenario where a complex calculation happens within a stored procedure which produces around 1 Lac records at the end of calculation. These 1 Lac records needs to be stored to a table. Everytime, this is calculated for the same parameter, it deletes previous result and inserts again. Since Delete\ Insert involves huge number of records, Lock Escalation happens and 'Table Lock' is applied. As the concurrent users increase, the response time from the Stored Proc increases drastically. Please suggest how else this can be done to bring down the response time. Th...

Adding new credit card account converts existing checking account
I have a checking account setup in Money 2006 small business from Wachovia Bank (without any online options). I am trying to add a credit card account from the same bank (again with no online options). After the account setup is finished, I check my account list. The checking account has moved from the Bank Accounts group to the Credit Accounts group (all transactions in tact). To reverse the problem, I add a new Bank Account from Wachovia, and everything changes back. Is it not possible to add more than one account type from the same bank? tia, Bob M.. ...

Add source data to an existing chart?
Hello! I am building a chart to display, by date, data in columns G (breakfast), D (lunch), and J (dinner). I can get the chart with dates along one axis and column G along the other, but how do I add the data from D and J to create a stacked bar, to show for each day how each of the three columns compare and give a daily total for all three meals? :confused: THANKS TO EVERYONE FOR YOUR HELP! :) -- navychef ------------------------------------------------------------------------ navychef's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29457 View this threa...

Location Availability
Can anyone tell me if it is possible to setup a location or user for a location so that when people schedule meetings they can see if the area they want to hold the meeting in is available. For example we have a meeting room and a showroom, so I want people to be able to check the availability of these rooms directly in outlook when creating a meeting request. Thank you. I've seen it done with Outlook -- JoAnn Paules MVP Microsoft [Publisher] "Tim" <Myemail@here.com> wrote in message news:11n7egikgheercc@corp.supernews.com... > Can anyone tell me if it i...

XP Themes on Forms
Sorry to re-post this, but I still need a little assistance. "thatsalok" <thatsalok@NO_gmail.com_I_DONT_NEED_SPAM> wrote in message news:%23hl9kRlFFHA.2508@TK2MSFTNGP10.phx.gbl... > You need to Enable Xp theme in your Appliation look for more info here > http://www.developer.com/net/asp/article.php/3101831 > > > -- > > With Regards > Alok Gupta > Visit me at http://alok.bizhat.com > > "I think this will Help" > "axis" <nospam@nospam.org> wrote in message > news:E42dndp3Y8E8KYvfRVn-ig@com...

Update Query does not clear all records immediately
Hi All, I have a form on which there are several "Flag" text fields which users use to mark records. Next to each flag field there is a clear button that runs the following code: Private Sub btn_ClSA1Flag1_Click() On Error GoTo Err_btn_ClSA1Flag1_Click Dim stDocName As String stDocName = "QryUpd_ClearSA1Flag1" DoCmd.OpenQuery stDocName, acNormal, acEdit Me.Requery 'DoCmd.OpenQuery stDocName, acNormal, acEdit Exit_btn_ClSA1Flag1_Click: Exit Sub Err_btn_ClSA1Flag1_Click: msgbox Err.Description Resume Exit_btn_ClSA1Flag1_Click ...

All my Product CDF import records are being rejected
I have carefully followed the CDF documentation regarding the mandatory fields yet all my products in the CDF are being rejected. The listed mandatory fields are simply: Product ID (int) ProductName (char) ProductNumber (char) I am also setting the StatusCodeName and StateCodeName fields to 'Active' I wonder whether in fact other fields are required - eg. Unit Group? All suggestions gratefully received! BillB I know that other data migration tools cannot give you access to StateCode fields. In anycase, you need not set the values of these fields as "Active" is the ...

what other charting software exists?
I am frustrated with Excel's limited charting ability. To produce many effects, you must fragment your data and trick Excel with it, and to my mind this makes the data table confusing to anyone who might look at it apart from the creator, and also essentially eliminates the ability to change data in the table and see the change immediately on the chart. I understand other software may be used for charting, but I haven't been able to find anything on Google because apparently the word "charting" usually refers to the charting of stocks, so all the pages I find refer to softwa...

Set up alias email address for existing user
Hello I am new to exchange 2003, and I need to set up 2 alias address for sales@company.com and support@company.com to be forwarded to user@company.com. I tried to setting up a contact for sales@comapny.com, but then I couldn't forward that to user@company.com. How do I get this done? Thanks in advance for the assist. Aloha newbee100, Just go to Active Director Users & Computers, find the user for user@company.com, open their properties and go to the e-mail addresses tab. You can add the additional addresses there. -Ben- Ben M. Schorr - MVP http://www.rolandschorr.com Micr...

Location Location Location? No, Traffic Traffic Traffic! #8
Do you want a great backend product that is selling like crazy this week? My readers are running to get this NEW CONCEPT! I�m selling more this week than I ever did in the past months with any other program. My commissions are climbing very fast. I�m really excited! This is the best ad copy I�ve read in months! It sells. If you haven't felt the shockwave yet, stop everything you are doing and read this to be one of the first promoters. The inner circles of the shockwave are raking in the profits like fury. This will pay us for years to come as it is the first �Wave� of the NEW I...

Chart Location Choice
I have one spreadsheet with lots of charts but for some reason I cannot select the chart location. The only one allowed is "As new sheet". The alternate ""As object in" is ghosted out! what did I do wrong?? I have checked tools-option? I disabled macros? Is the sheet protected, preventing insertion of any objects. Regards, Peter T "Geoff" <Geoff@discussions.microsoft.com> wrote in message news:598ADFDC-9F61-40F0-AB4A-08023022BB9F@microsoft.com... >I have one spreadsheet with lots of charts but for some reason I cannot > select the chart...

Where to find previous updates for Office 2008?
Hi I tried to install the service pack for office 2008 but when I started installation it gave an error because I need some extra updates not installed previously. Where can I find all the updates available for Office 2008? How to determine which updates should I need? Thanks Which Service Pack did you try to install -- there are 2 of them for Office 2008. Both include the interim updates which preceded them so you shouldn't need to go any further back than SP1 (12.1.0) plus SP2 (12.2.0) followed but 12.2.1 (the latest interim update). You should be able to run Check fo Updates fo...

Business Units record seperation
I have an organization with a top level Organization and the 4 subsidiary B U’s. This is all set up in CRM and the CEO can see leads across all BU's. The problem that I am having is that a sales manager can also see all leads through out the organization also ie, leads that they do not own and have not been assigned to them. The sales manager BU is set so that members can only see (read) the leads in their own business unit. Structure – Organization – CEO Business Unit A – sales manager A Business Unit B– sales manager B Business Unit C– sales manager C Business Unit D– sales...