Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I select a value from a field in an ACCESS unbound datasheet

Posted on 2014-12-30
6
Medium Priority
?
251 Views
Last Modified: 2015-01-06
H Experts,
How do I select a value from a field in an ACCESS unbound datasheet?  My unbound datasheet displays the following fields:  JobID, JobStartDate, JobEndDate, JobAddress.  When  a row/record is selected in the datasheet, I want to put the value of the JobID field in a variable.
I found the following code below, but it only provides the current records position/location in the datasheet (i.e. 1, 2, 3, etc..)

Dim i As Integer
i = Me.dataDisplaySubform.Form.CurrentRecord
MsgBox (CStr(i))

Open in new window


Is it possible to do what I need?  How?  Please provide examples.

Thank you all very much in advance,
mrotor
0
Comment
Question by:mainrotor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 2000 total points
ID: 40523739
i = Me.dataDisplaySubform.Form!JobID


ET
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40523769
Your data sheet is bound.  Otherwise it couldn't show more than a single row.

You are referring to the CurrentRecord property, that is why you are getting the record's ordinal number.  If you want to get something from a control, you need to refer to the control name.

Me.SaveJobID = Me.subformname.Form!JobID
0
 
LVL 31

Expert Comment

by:hnasr
ID: 40523787
ET's comment is fine.
Alternative:
i = Me!dataDisplaySubform.Controls("JobID")

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Expert Comment

by:Nick67
ID: 40523894
Your answer is already given.
You were close, but .CurrentRecord is a property that gives you a number related to your position in the records of an object.  If you had 20 records showing and the cursor is in a control in the ninth row, .CurrentRecord would give you '9'

But your syntax is good up to that point

Dim i As Integer
i = Me.dataDisplaySubform.Form
.CurrentRecord
MsgBox (CStr(i))

Where it gets tricky is after the Form
! operators vs . operators is not well understood (and maybe not even by me -- we'll see if I get corrected here :)
The ! operator gives you the members of a collection
The . operator gives you the properties of an object
Sometimes these two things overlap (Controls are one area of overlap.  Each control is a member of the Controls collection, but they are also defined as properties of the Me object)

You only get Intellisense for the . operator and not for the ! operator.
That trips up a lot of folks when it comes to referencing subforms & subreports
The default collection for a form or report is its Controls collection.

So you want the subform's JobID control
Ok
'I = Me | Mysubform control | treat it as a form
i = Me.dataDisplaySubform.Form.Controls("JobID").Value
That's as explicit as it gets
Now, the Controls collection is the default collection for a form
And the Value property is the default property for a control, so
i = Me.dataDisplaySubform.Form!JobID
is the briefest way of writing the syntax.

Hope this helps

Nick67
0
 
LVL 31

Expert Comment

by:hnasr
ID: 40524103
>>! operators vs . : Late binding vs immediate binding

Try this in a any event Sub, say Form_Current with a break point at Private Sub line

Me.s.w = 4   ' this immediate binding, it also allow for intellisense to help in writing code.

When you step into procedures it shows an error on .s unless it happens to have a correct values.
Also this error stops you from using immediate window from checking your code.

But with this

Me!s!w =4  'this is late binding, it checks code when time to execute.

Code highlights then when you press Step into again, it displays an error.
You can use immediate window to check code.
0
 
LVL 85
ID: 40524138
How do I select a value from a field in an ACCESS unbound datasheet?
As Pat mentioned here, and I mentioned in your previous question, you're not using an UNBOUND datasheet. If you were, you'd see a single record and nothing more.

I mention this because it's important to use the correct terminology when asking questions like this. An "unbound" form is one that has no Recordsource, and where the controls have no ControlSource. Even if you do not initially "bind" a form to a Recordsource, if you do at some point (and if your controls are bound), then you're using a bound form. In an unbound form, you're entirely responsible for grabbing data from a datasource, and filling the controls. You're further responsible for handling any data edits/additions/deletions. There's rarely a need to do this in Access - and there is never a need in a datasheet form.

If you use the wrong terminology, you run the risk of getting incorrect or misguided advice. In this case, since the participants are all very experienced you don't have much to worry about, but these sorts of things can bite you.

Long story short - be sure you understand the terminology you're using, and make sure you convey that information to the Experts correctly. You'll have a much better EE experience.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

610 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