Access SQL Lookup Function

  • Thread starter Thread starter jake
  • Start date Start date
J

jake

I am trying to write a function that does a simple SQL lookup and
returns the value from the SELECTed field.

In SQL I would do this as a stored procedure along the lines of

BEGIN
RETURN
(
SELECT data
FROM table
WHERE (field1 = @PARM_X)
)
END

I am relatively new to Access (doing a project for a friend who can't
afford SQL Server) and I can't seem to find a lookup function or a way
to make this simple VBA function (sorry, I am slow).

What I have is an Access Form that shows a record from table X, I want
to populate other fields on that form from other tables based on the
record index of the form's record.
 
(e-mail address removed)
m:
I am trying to write a function that does a simple SQL lookup and
returns the value from the SELECTed field.

In SQL I would do this as a stored procedure along the lines of

BEGIN
RETURN
(
SELECT data
FROM table
WHERE (field1 = @PARM_X)
)
END

I am relatively new to Access (doing a project for a friend who
can't afford SQL Server) and I can't seem to find a lookup
function or a way to make this simple VBA function (sorry, I am
slow).

What I have is an Access Form that shows a record from table X, I
want to populate other fields on that form from other tables based
on the record index of the form's record.
Click to expand...
You do not need to createa function, it's already part of Access.
'if param_x is numeric
It is dLookup("data","table","Field1=" & Param_X)
'if param_x is string
It is dLookup("data","table","Field1=""" & Param_X & """")
 
If your friend cannot afford one of the full version SQL-Server, why doesn't
he (or you) take a look at SQL-Server Express 2008?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
It is dLookup("data","table","Field1=""" & Param_X & """")
Click to expand...

Thanks Bob that looks much easier. I had hoped there was something in
there but I couldn't search it up myself.
 
can't
afford SQL Server
Click to expand...

As others mentioned there is several free editions of sql server.

The problem here is not using Oracle, SQL server, MySql, or the JET database
engine.

Access is not the database engine like Sql server. After you choose the
database engine of your choice, then you have to choose a set of
development tools with which to build the interface with.

You can't build forms with sql server. You can not build forms
with MySql, or with Oracle or <insert favorite database here>

So ms-access is a developers tool like vb, c++ etc and we should
try not to confuse it with a database engine.
In SQL I would do this as a stored procedure along the lines of
Click to expand...

Right but then where is that data going to be feed to?
I can't seem to find a lookup function or a way
to make this simple VBA function (sorry, I am slow).

What I have is an Access Form that shows a record from table X
Click to expand...

Ok so we already have data in the form. This is really a VERY different
question then that of a stored procedure.
I want
to populate other fields on that form from other tables based on the
record index of the form's record.
Click to expand...

Are you saying table, or table(s). Again, a VERY different question if you
mean "table" or more then one table. You don't really populate controls on a
form. Forms have "controls" that are bound to the forms underlying
recordset. So it is the forms recordset (data source) that will change here.

Since the form is already bound to that table (or query), then we simply
"move" that recordset pointer to the required record in the table. Then
all the text boxes will display the correct information. Forms allow you to
edit ONE record at a time.

The simple way to do this is have users place their cursor in whatever text
box on the form and then hit ctrl-f.

If you want a "pick list" that the user chooses from (or enters a value)
then bring up the form in design mode, and drop a combo box onto the form
(use the forms wizards). The combo box wizard can build you a search combo
box.

The wizard will
write the code for you and then you can look at that code. So, that combo
box will allow you to select a value the table such as name, or company, or
whatever. In fact, when you build the combobox, have the 1st column as the
primary key (usually "ID"), and then the 2nd column can be the field you
want to search by, say Lastname, or perhaps even company.

When the user selects from that pick list, the form will then move to that
record, and all controls will populate/display the values for you.

In the current context here you really don't need a procedure to "look up" a
value.

If I missed your question and you you need to display values from other
tables
then I assuming those other tables are "related" to the main table in some
way?
If yes, then you use what is called a sub-form. So sub-forms allow you to
display other related tables. I explain sub-forms here:
http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html

Often we use continues forms to display "related" data from other tables.
Here is some screen shots of this concept:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Note how in the above we use "forms" to prompt the user, and there little
if anything to do with stored procedures.

Note that we don't have to write any code at all to "display" those related
table data in a form when we use a sub-form. The display and even updating
of "child" records is handled automatic for you when you use a sub-form.

So, to make a long story short, for your scenario a combo box that selects
the record and moves the form to that record to populate the contorls is
likly the best starting point here.
 
Hi Jake,

I see someone already mentioned dlookup.

Another way - somewhat closer to your SQL approach would be this Access
query.

(assuming string data)
SELECT data FROM table WHERE field1='" & forms!myForm!myControl & ''';"

Note there are single quotes inside the double-quotes - when this runs, the
forms!myForm!myControl will be replaced by the value it represents and, if a
string, IMO single-quotes are the easiest to 'see' and use to signal a
literal value (rather than double-quotes embedded in double-quotes).

If a numeric vlaue, just drop the single-quotes.

The collection reference deserves a bit of explanation. the ! (bang)
indicates a collection reference. Most collections have a default object -
the default object contained in the Forms collection is the form. So -
Form!myForm is a reference to myForm. In turn, controls are the default
object of the form collection so Forms!myForm!myControl gets you to the
control.

Another way of getting a reference to the same item (the control whose value
you wish to use) Application.CurrentDb.Forms("myForm").Controls("myControl")
gets you to the same place by using thename of an item as the index of the
collection to refer to that specific item of the collection. The
Application.CurrentDb is not always necessary but it is the
grandfather!parent of the Forms collection so I included it. The application
collection is the default collection of Access - in many common contexts
CurrentDb is the assumed parent.

If you were constructing this within a VBA module of the form than you could
reference Me!myControl with the same effect because Me would be a reference
to the form that is the parent of the code module.

Being able to reference the contents of a form control within a query and
also within events using VBA code allows the programmer to leverage the
particular assets offered by Access to his advantage.

Hope this helps,
Gordon
 
Back
Top