Solved

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

Posted on 2003-12-08
7
412 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 Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 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

8 Experts available now in Live!

Get 1:1 Help Now