DLookup Issues

  • Thread starter Thread starter vladi16
  • Start date Start date
V

vladi16

Looking for someone with a lot of patience to answer my DLookup question.
Let me start by saying I am weak at best when it comes to expressions/codes
so be gentle.

I have a table, tbltest, that has the following fields:

testID = Autonumber
test# = Identifies the particular test. I have several 100 series tests
(101, 102, 103 etc.), 200 series, 300 series, etc.
rule = Identifies the particular rule number I am testing my employee on
(can be numeric or text)
rulename = The name of the rule I am testing the employee on.
reference = The book which the particular rule comes out of (ex: Safety Rule
Book, Mechanical Rule Book, Operating Rule Book, etc.)

OK, all that being said... On my form I would like to enter, by way of my
drop down box, the test# and have the associated rule, rulename, and
reference automatically appear.

Not sure if this is pertinent, but I will mention it: On my tbltest table
in my rule# field I have all unique entries, except for when you get down to
the 600 series. The 600 series is sort of my miscelaneous group of tests. I
have several test# that equal 601, but each of them has there own rule,
rulename, and reference associated with it.

Hope this all makes sense... Thanks, and I apologize in advance for my silly
follow up questions.

Doug
 
Let me add that before you throw the standard =DLookup(blah, blah, blah...)
at me, I have no idea where o even enter that at. So lets back up and start
me with: Does that go on the form, on the table, and in which field on it etc.

Thanks
 
Looking for someone with a lot of patience to answer my DLookup question.
Let me start by saying I am weak at best when it comes to expressions/codes
so be gentle.

I have a table, tbltest, that has the following fields:

testID = Autonumber
test# = Identifies the particular test. I have several 100 series tests
(101, 102, 103 etc.), 200 series, 300 series, etc.
rule = Identifies the particular rule number I am testing my employee on
(can be numeric or text)
rulename = The name of the rule I am testing the employee on.
reference = The book which the particular rule comes out of (ex: Safety Rule
Book, Mechanical Rule Book, Operating Rule Book, etc.)

OK, all that being said... On my form I would like to enter, by way of my
drop down box, the test# and have the associated rule, rulename, and
reference automatically appear.

Not sure if this is pertinent, but I will mention it: On my tbltest table
in my rule# field I have all unique entries, except for when you get down to
the 600 series. The 600 series is sort of my miscelaneous group of tests. I
have several test# that equal 601, but each of them has there own rule,
rulename, and reference associated with it.

Hope this all makes sense... Thanks, and I apologize in advance for my silly
follow up questions.
Click to expand...

It's not necessary to use DLookUp *at all* for this purpose; you can do it
more simply!

Just base the Combo Box (your "drop down") on a Query including all five
fields. For instance, the SQL view of this query might be

SELECT [TestID], [Test#], [Rule], [RuleName], [Reference] FROM tblTest ORDER
BY [Test#];

Use this query as the combo's Row Source. Set the combo's Column Count
property to 5 so that all five fields are included. Set the combo's
ColumnWidths property to display whichever columns you want the user to see
when the combo is dropped down - for example, if you just want to see the
Test# use a ColumnWidths property of

0";0.5";0";0";0"

Zero width columns will still be "there" but just not displayed.

Then elsewhere on the form put a textbox labeled Rule with a control source

=comboboxname.Column(2)

You use 2 to display the third column in the Rowsource query - the Column
property is zero based, so 0 would be the TestID, 1 the Test# and so on.
 
Worked like a charm... Thanks John.

John W. Vinson said:
Looking for someone with a lot of patience to answer my DLookup question.
Let me start by saying I am weak at best when it comes to expressions/codes
so be gentle.

I have a table, tbltest, that has the following fields:

testID = Autonumber
test# = Identifies the particular test. I have several 100 series tests
(101, 102, 103 etc.), 200 series, 300 series, etc.
rule = Identifies the particular rule number I am testing my employee on
(can be numeric or text)
rulename = The name of the rule I am testing the employee on.
reference = The book which the particular rule comes out of (ex: Safety Rule
Book, Mechanical Rule Book, Operating Rule Book, etc.)

OK, all that being said... On my form I would like to enter, by way of my
drop down box, the test# and have the associated rule, rulename, and
reference automatically appear.

Not sure if this is pertinent, but I will mention it: On my tbltest table
in my rule# field I have all unique entries, except for when you get down to
the 600 series. The 600 series is sort of my miscelaneous group of tests. I
have several test# that equal 601, but each of them has there own rule,
rulename, and reference associated with it.

Hope this all makes sense... Thanks, and I apologize in advance for my silly
follow up questions.
Click to expand...

It's not necessary to use DLookUp *at all* for this purpose; you can do it
more simply!

Just base the Combo Box (your "drop down") on a Query including all five
fields. For instance, the SQL view of this query might be

SELECT [TestID], [Test#], [Rule], [RuleName], [Reference] FROM tblTest ORDER
BY [Test#];

Use this query as the combo's Row Source. Set the combo's Column Count
property to 5 so that all five fields are included. Set the combo's
ColumnWidths property to display whichever columns you want the user to see
when the combo is dropped down - for example, if you just want to see the
Test# use a ColumnWidths property of

0";0.5";0";0";0"

Zero width columns will still be "there" but just not displayed.

Then elsewhere on the form put a textbox labeled Rule with a control source

=comboboxname.Column(2)

You use 2 to display the third column in the Rowsource query - the Column
property is zero based, so 0 would be the TestID, 1 the Test# and so on.
Click to expand...
 
John, your help gave me what I asked for, thanks, but has led me to another
issue.

First, let me say I have changed the field names test# to testnumber and
rule to rule number.

Anyway, my issue is: When I fill out and try and save my form I get the
following message "You must enter a value in the 'tbltretests' field.

My thought/desire was that when I filled in the testnumber field and the
other three fields popped up in their respective text boxes they would save
to the table too. For what it is worth, I do have the "Required" field set
to yes on each of the three text boxes. When I change that setting to No I am
able to save the form but those three fields remain blank in the table.

Any thoughts? Thanks.

John W. Vinson said:
Looking for someone with a lot of patience to answer my DLookup question.
Let me start by saying I am weak at best when it comes to expressions/codes
so be gentle.

I have a table, tbltest, that has the following fields:

testID = Autonumber
test# = Identifies the particular test. I have several 100 series tests
(101, 102, 103 etc.), 200 series, 300 series, etc.
rule = Identifies the particular rule number I am testing my employee on
(can be numeric or text)
rulename = The name of the rule I am testing the employee on.
reference = The book which the particular rule comes out of (ex: Safety Rule
Book, Mechanical Rule Book, Operating Rule Book, etc.)

OK, all that being said... On my form I would like to enter, by way of my
drop down box, the test# and have the associated rule, rulename, and
reference automatically appear.

Not sure if this is pertinent, but I will mention it: On my tbltest table
in my rule# field I have all unique entries, except for when you get down to
the 600 series. The 600 series is sort of my miscelaneous group of tests. I
have several test# that equal 601, but each of them has there own rule,
rulename, and reference associated with it.

Hope this all makes sense... Thanks, and I apologize in advance for my silly
follow up questions.
Click to expand...

It's not necessary to use DLookUp *at all* for this purpose; you can do it
more simply!

Just base the Combo Box (your "drop down") on a Query including all five
fields. For instance, the SQL view of this query might be

SELECT [TestID], [Test#], [Rule], [RuleName], [Reference] FROM tblTest ORDER
BY [Test#];

Use this query as the combo's Row Source. Set the combo's Column Count
property to 5 so that all five fields are included. Set the combo's
ColumnWidths property to display whichever columns you want the user to see
when the combo is dropped down - for example, if you just want to see the
Test# use a ColumnWidths property of

0";0.5";0";0";0"

Zero width columns will still be "there" but just not displayed.

Then elsewhere on the form put a textbox labeled Rule with a control source

=comboboxname.Column(2)

You use 2 to display the third column in the Rowsource query - the Column
property is zero based, so 0 would be the TestID, 1 the Test# and so on.
Click to expand...
 
Meant to say message said: "You must enter a value in the
'tbltretests.rulenumber' field.



vladi16 said:
John, your help gave me what I asked for, thanks, but has led me to another
issue.

First, let me say I have changed the field names test# to testnumber and
rule to rule number.

Anyway, my issue is: When I fill out and try and save my form I get the
following message "You must enter a value in the 'tbltretests' field.

My thought/desire was that when I filled in the testnumber field and the
other three fields popped up in their respective text boxes they would save
to the table too. For what it is worth, I do have the "Required" field set
to yes on each of the three text boxes. When I change that setting to No I am
able to save the form but those three fields remain blank in the table.

Any thoughts? Thanks.

John W. Vinson said:
Looking for someone with a lot of patience to answer my DLookup question.
Let me start by saying I am weak at best when it comes to expressions/codes
so be gentle.

I have a table, tbltest, that has the following fields:

testID = Autonumber
test# = Identifies the particular test. I have several 100 series tests
(101, 102, 103 etc.), 200 series, 300 series, etc.
rule = Identifies the particular rule number I am testing my employee on
(can be numeric or text)
rulename = The name of the rule I am testing the employee on.
reference = The book which the particular rule comes out of (ex: Safety Rule
Book, Mechanical Rule Book, Operating Rule Book, etc.)

OK, all that being said... On my form I would like to enter, by way of my
drop down box, the test# and have the associated rule, rulename, and
reference automatically appear.

Not sure if this is pertinent, but I will mention it: On my tbltest table
in my rule# field I have all unique entries, except for when you get down to
the 600 series. The 600 series is sort of my miscelaneous group of tests. I
have several test# that equal 601, but each of them has there own rule,
rulename, and reference associated with it.

Hope this all makes sense... Thanks, and I apologize in advance for my silly
follow up questions.
Click to expand...

It's not necessary to use DLookUp *at all* for this purpose; you can do it
more simply!

Just base the Combo Box (your "drop down") on a Query including all five
fields. For instance, the SQL view of this query might be

SELECT [TestID], [Test#], [Rule], [RuleName], [Reference] FROM tblTest ORDER
BY [Test#];

Use this query as the combo's Row Source. Set the combo's Column Count
property to 5 so that all five fields are included. Set the combo's
ColumnWidths property to display whichever columns you want the user to see
when the combo is dropped down - for example, if you just want to see the
Test# use a ColumnWidths property of

0";0.5";0";0";0"

Zero width columns will still be "there" but just not displayed.

Then elsewhere on the form put a textbox labeled Rule with a control source

=comboboxname.Column(2)

You use 2 to display the third column in the Rowsource query - the Column
property is zero based, so 0 would be the TestID, 1 the Test# and so on.
Click to expand...
Click to expand...
 
Back
Top