Solved

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

Posted on 2003-12-08
7
415 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access 2012, Split Form 2 39
Search a table based on a "like" search 6 44
Is there a way to link Access 2003 app to an xlsx sheet? 6 24
Comparison query - 4 columns 9 25
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

827 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