Solved

Form Question:  Store a ControlSource expression calculated value in RecordSource field

Posted on 2003-12-08
7
413 Views
Last Modified: 2011-10-03
Hello all,

I have a data entry form where the user enters an AdHocID in a textbox and selects a location from a listbox.  This listbox contains an associated TreatmentTypeID.    I'd like to have another text box that shows a calculated SampleID = AdHocID + (100000)*(TreatmentTypeID) and then stores that calculated value in the SampleID field of the form's RecordSource.  I don't understand how to specify the calculated value to be stored in the SampleID field of the recordsource.  Am I missing something?  Is this even possible?  

Here's the expression I'm using in the form:

=[AHID]+(100000*(Forms![Ad Hoc Entry Sheet]![Location and TreatmentType IDs].Column(4))

Unfortunately this isn't even showing up correctly in the form and just returns #NAME?.  HELP!?!

Chad
0
Comment
Question by:eldo64
  • 3
  • 2
  • 2
7 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9900805
Hey eldo64!

  =([AHID]+(100000*(Forms![Ad Hoc Entry Sheet]![Location and TreatmentType IDs].Column(4)))


  You need to work on shortening those form and control names.  There's no need for them to be that long.

  What data is in column 4, and are you really referring to the fourth column or column number 4 where the column numbers begin at zero.  If you are pointed at the wrong column this could be the issue.
regards
Jack
0
 

Author Comment

by:eldo64
ID: 9900985
Jack,

I hear you on the long names...sorry.  The form contains a listbox that selects LocationID.  Instead of the "Location and TreatmentType IDs" query, I have also used a SQL statement:

SELECT LocationID.LocationID, LocationID.TreatmentTypeName, LocationID.LocationName, LocationID.TreatmentTypeID FROM LocationID;

Here you can see that TreatmentTypeID is the fourth field.  After checking out some more help files, it looks like I should be using Column(3) since the column numbers begin at zero, but that doesn't seem to be the correct reference either.

What do you think?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9901120
Have you tried that theory yet?  It's looking like exactly what I thought it might be.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 3

Expert Comment

by:serbach
ID: 9901143
Chad,

First, remove the calculation from the SampleID text box.

Second, I think that you should set the value for the SampleID text box in your listbox After Update event procedure. You didn't give the Name property for the listbox so I'll refer to it as lstYourList:

SampleID = AHID + 100000 * lstYourList.Column(3, lstYourList.ListIndex)

Then after you've selected the location in the lstYourList, the SampleID will be filled with the calculated value. The ListIndex is what you need to select the proper column AND row.

Sincerely,

Steve Erbach
Scientific Marketing
Neenah, WI
0
 

Author Comment

by:eldo64
ID: 9908447
Steve,

You're help is very much appreciated.  I'm having trouble getting the SampleID calulcation to work in the After Update event procedure.  Do I enter it as an expression?  Or as a macro?  I can't seem to get this After Update thing to work...

Chad
0
 
LVL 3

Accepted Solution

by:
serbach earned 500 total points
ID: 9911820
Chad,

The calculation should be entered as Code using the Code Builder. That is, select the List Box in question on your form (I called it lstYourList), then click on the Event tab in the List Box properties window, select the After Update event, click on the ellipsis button (...), and select Code Builder. This will open the VBA editor with the cursor positioned in between Private Sub lstYourList_AfterUpdate() and End Sub. That is where you place the calculation:

Private Sub lstYourList_AfterUpdate()
   SampleID = AHID + 100000 * lstYourList.Column(3, lstYourList.ListIndex)
End Sub

Of course you use the real name for the List Box that you've given it. If you haven't given the List Box a name (on the Other tab of the properties window) then you should do so to make any other coding easier. Then when you Run the form and change the value in the List Box, the After Update event will fire and will stuff the SampleID Text Box with the results of the calculation.

Does this help?

Sincerely,

Steve Erbach
Scientific Marketing
Neenah, WI
0
 

Author Comment

by:eldo64
ID: 9913513
Steve,

Thanks for seeing me through to the final solution on that one!  Works great!

Chad
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 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

13 Experts available now in Live!

Get 1:1 Help Now