DLookup Problems

[swc76801]

I'm still in the very early learning stage with Access. I'm trying to
use the DLookup feature using the following code:
=DLookUp("[Rate]","Rates","Rates![RoomID] = [RoomID]"). I have
verified the code by using Ctrl-G, but it will not work in my form.
Last night I was working on this using a different form and had a
similar problem. I changed some of the property settings and had it
working. Unfortunately 1) I don't remember which settings I changed
and 2) I deleted the form because it wasn't exactly what I wanted.

I would really appreciate some guidance here. Thanks in advance for
your help!

Steve

 

[Guest]

DLookup("Rates", "Rates", "RoomID = " & Forms![YourOpenFormName]!RoomID)

CW

PS: Not sure it's a very good idea to name tables and fields the exact same
name.

 

[swc76801]

DLookup("Rates", "Rates", "RoomID = " & Forms![YourOpenFormName]!RoomID)

CW

PS: Not sure it's a very good idea to name tables and fields the exact same
name.

I'm still in the very early learning stage with Access. I'm trying to
use the DLookup feature using the following code:
=DLookUp("[Rate]","Rates","Rates![RoomID] = [RoomID]"). I have
verified the code by using Ctrl-G, but it will not work in my form.
Last night I was working on this using a different form and had a
similar problem. I changed some of the property settings and had it
working. Unfortunately 1) I don't remember which settings I changed
and 2) I deleted the form because it wasn't exactly what I wanted.

I would really appreciate some guidance here. Thanks in advance for
your help!

Steve- Hide quoted text -

- Show quoted text -

I made the changes, but the information isn't pulled into the form.
Once again, I checked the code using Ctrl+G and it appears to work
fine. Last night when I has this working, I changed some of the
property settings -- I think it was on the "Data" tab. But nothing I
try today makes any difference. I've deleted the form and started with
a new clean version, but still nothing seems to work. I will change
the name for the table to avoid any potential problems. Sorry for
being so dumb.

 

[Guest]

Sigh
Should be:

DLookup("Rate", "Rates", "RoomID = " & Forms![YourOpenFormName]!RoomID)

CW going home.

DLookup("Rates", "Rates", "RoomID = " & Forms![YourOpenFormName]!RoomID)

CW

PS: Not sure it's a very good idea to name tables and fields the exact same
name.

I'm still in the very early learning stage with Access. I'm trying to
use the DLookup feature using the following code:
=DLookUp("[Rate]","Rates","Rates![RoomID] = [RoomID]"). I have
verified the code by using Ctrl-G, but it will not work in my form.
Last night I was working on this using a different form and had a
similar problem. I changed some of the property settings and had it
working. Unfortunately 1) I don't remember which settings I changed
and 2) I deleted the form because it wasn't exactly what I wanted.

I would really appreciate some guidance here. Thanks in advance for
your help!

Steve

 

[John W. Vinson]

I'm still in the very early learning stage with Access. I'm trying to
use the DLookup feature using the following code:
=DLookUp("[Rate]","Rates","Rates![RoomID] = [RoomID]"). I have
verified the code by using Ctrl-G, but it will not work in my form.
Last night I was working on this using a different form and had a
similar problem. I changed some of the property settings and had it
working. Unfortunately 1) I don't remember which settings I changed
and 2) I deleted the form because it wasn't exactly what I wanted.

I would really appreciate some guidance here. Thanks in advance for
your help!

Steve

Well... DLookUp is expensive to execute and probably not the best way to do
this anyway. Your DLookUp as written will find all records in the Rates table
where the RoomID is equal to itself, without any reference to your form at
all!

What is the Recordsource for your form? How are you selecting the RoomID?

John W. Vinson [MVP]

 

[swc76801]

I'm still in the very early learning stage with Access. I'm trying to
use the DLookup feature using the following code:
=DLookUp("[Rate]","Rates","Rates![RoomID] = [RoomID]"). I have
verified the code by using Ctrl-G, but it will not work in my form.
Last night I was working on this using a different form and had a
similar problem. I changed some of the property settings and had it
working. Unfortunately 1) I don't remember which settings I changed
and 2) I deleted the form because it wasn't exactly what I wanted.

I would really appreciate some guidance here. Thanks in advance for
your help!

Steve

Well... DLookUp is expensive to execute and probably not the best way to do
this anyway. Your DLookUp as written will find all records in the Rates table
where the RoomID is equal to itself, without any reference to your form at
all!

What is the Recordsource for your form? How are you selecting the RoomID?

John W. Vinson [MVP]

John

The form is pulling data from a table called "Rooms'. The fields are
"RoomID" (Primary Key), "Room Name" and "Rate". "Rate is a currency
field.

As I mentioned in my first post, I'm very much the novice here and
realize that a little knowledge is dangerous. I definitely have a lot
that I need to learn.

The objective is to be able to reserve one of the rooms listed in the
Rooms table, and then calculate the rental fee. The way that I'm
trying to do this is to have a form that pulls data from
"Customers" (with name address etc), Rooms and Events (EventID, RoomID
CustomerID, Date of Event, Start Time, End Time, Rate, Time and
Charges.

CustomerID and RoomID are lookup tables that pull the appropriate
data. the date and time fields store the times the room is reserved.
My objective was to pull the correct rate after the RoomID has been
selected. This dollar amount would then be multiplied by "Time" to
calculate "Charges. "Time" would be calculated from Start Time and End
Time.

Based on my limited knowledge, I'd thought the DLookup would be the
best way to get the Rate value. I'm definitely not locked into this
solution and am more than willing to learn a better way to do this.
What really gets me is that I actually had this working last night and
decided that the form wasn't what I needed so I deleted it and made
modifications to the various tables that I'm using. I was so sure that
if I could get it to work once, I could do it again. Boy way I wrong!

 

[John W. Vinson]

John

The form is pulling data from a table called "Rooms'. The fields are
"RoomID" (Primary Key), "Room Name" and "Rate". "Rate is a currency
field.

If you just want to display the current rate, you can use:

=DLookUp("Rate", "Rates", "[RoomID] = " & [RoomID])

Note the placement of the quote: the RoomID inside the quotes refers to the
table field in the Rates table; the one outside the quotes refers to the Rooms
form control (or the field).

However, you may want to *store* the rate looked up from the Rates table into
a form control to be stored. You really should (and may, I can't tell from
here!) have a table of room rentals - you should NOT be storing (say) the date
that a room was rented, or to whom, or for how much, in the Rooms table;
you're hopefully going to rent the same room on another date, to someone else,
for perhaps a different price!

What IS the structure of your tables?

John W. Vinson [MVP]

 

[AccessVandal via AccessMonster.com]

Hi Steve,

At first you said,

"=DLookUp("[Rate]","Rates","Rates![RoomID] = [RoomID]")"

the table name was "Rates", now you said the table name was "Rooms".

So, what is the table name?

(e-mail address removed) wrote:
The form is pulling data from a table called "Rooms'. The fields are
"RoomID" (Primary Key), "Room Name" and "Rate". "Rate is a currency
field.

If it is Name "Rooms", change it to something like...

=DLookUp("[Rate]","Rooms","RoomID = [RoomID]")
or
=DLookUp("[Rate]","Rates","Rates![RoomID] = " & Me.RoomID)

 

[AccessVandal via AccessMonster.com]

Geezz!

the results of cut and paste.

=DLookUp("[Rate]","Rooms","RoomID = [RoomID]")
or
=DLookUp("[Rate]","Rooms","RoomID = " & Me.RoomID)

 

[SWC76801]

Geezz!

the results of cut and paste.

=DLookUp("[Rate]","Rooms","RoomID = [RoomID]")
or
=DLookUp("[Rate]","Rooms","RoomID = " & Me.RoomID)

AccessVandal wrote:
Hi Steve,

My ISP was down last night, so I couldn't reply to the various
messages. First of all, based on suggestions I received, I changed the
name for the table from Rate to Rooms. The table only contains the
RoomID, Room Name and Rate. I would very much like to be able to store
the data for Rate and not just display it, however; I haven't been
able to find the correct procedure to do that.

I've also tried both of the options provided by AccessVandal but
neither of them work in the form. As with other attempts, when I test
them with Ctrl+G they work as they should, but when used in the form,
they will not work. I'm obviously doing something really stupid here
that prevents them form working.

 

[AccessVandal via AccessMonster.com]

Hi Steve,

So, is one of the Dlookup method works? Which one?

There’s very little information why you need to record “Rate†into the
“Customers†table.

It’s a good idea to track changes (Rate) in the “Customers†Table only if the
Rate is always changing. Example, you want to find out what was the “Rateâ€
for the customer for last year. But of course, there are other methods
without the Column “Rate†in the “Customers†Table.

Having the column “Rate†in “Customer†table will be easier for a beginner.

We’ll need more input on how, where, etc. Form design.

My guess for such action is to use a combobox to autoFill the “Rate†on your
form but with very little details there’s not much we can go about.

Something like say….

Me.Rate = Me.ComboboxName.Column(1)

More likely we will use a combobox which is bound the “CustomerID†and in the
combo’s Rowsource, something like “SELECT CustomerDetail.CustomerID, Rooms.
Rate FROM CustomerDetail, Roomsâ€

I’m assuming there is a Table called “CustomerDetail†which is the list of
all your customer information.

If your combobox is name “CustomerID†and the field/Textbox is name “Rateâ€
We’ll have something like…

Me.Rate = Me.CustomerID.Column(1)

You put this into the combobox afterupdate event.

The “Column(1)†the the combobox rowsource query, column(0) is the CustomerID
and Column(1) is the “Rateâ€

Hope to get you something to start with.

SWC76801 wrote:

Geezz!

[quoted text clipped - 9 lines]
My ISP was down last night, so I couldn't reply to the various
messages. First of all, based on suggestions I received, I changed the
name for the table from Rate to Rooms. The table only contains the
RoomID, Room Name and Rate. I would very much like to be able to store
the data for Rate and not just display it, however; I haven't been
able to find the correct procedure to do that.

I've also tried both of the options provided by AccessVandal but
neither of them work in the form. As with other attempts, when I test
them with Ctrl+G they work as they should, but when used in the form,
they will not work. I'm obviously doing something really stupid here
that prevents them form working.

 

[swc76801]

Hi Steve,

So, is one of the Dlookup method works? Which one?

There's very little information why you need to record "Rate" into the
"Customers" table.

It's a good idea to track changes (Rate) in the "Customers" Table only if the
Rate is always changing. Example, you want to find out what was the "Rate"
for the customer for last year. But of course, there are other methods
without the Column "Rate" in the "Customers" Table.

Having the column "Rate" in "Customer" table will be easier for a beginner.

We'll need more input on how, where, etc. Form design.

My guess for such action is to use a combobox to autoFill the "Rate" on your
form but with very little details there's not much we can go about.

Something like say....

Me.Rate = Me.ComboboxName.Column(1)

More likely we will use a combobox which is bound the "CustomerID" and in the
combo's Rowsource, something like "SELECT CustomerDetail.CustomerID, Rooms.
Rate FROM CustomerDetail, Rooms"

I'm assuming there is a Table called "CustomerDetail" which is the list of
all your customer information.

If your combobox is name "CustomerID" and the field/Textbox is name "Rate"
We'll have something like...

Me.Rate = Me.CustomerID.Column(1)

You put this into the combobox afterupdate event.

The "Column(1)" the the combobox rowsource query, column(0) is the CustomerID
and Column(1) is the "Rate"

Hope to get you something to start with.

SWC76801 wrote:

[quoted text clipped - 9 lines]

My ISP was down last night, so I couldn't reply to the various
messages. First of all, based on suggestions I received, I changed the
name for the table from Rate to Rooms. The table only contains the
RoomID, Room Name and Rate. I would very much like to be able to store
the data for Rate and not just display it, however; I haven't been
able to find the correct procedure to do that.

I've also tried both of the options provided by AccessVandal but
neither of them work in the form. As with other attempts, when I test
them with Ctrl+G they work as they should, but when used in the form,
they will not work. I'm obviously doing something really stupid here
that prevents them form working.

Sorry that I didn't explain the version that was working better. What
happened was that I had the Dlookup function working on a table that I
deleted. The logic I was using to generate the table was faulty so I
deleted the table and built a new one. From then on I haven't been
able to get the function to work properly. Now I realize that it
wasn't the function I needed. I'm now tyring to learn how to do the
combobox. This definitely seems to be what I really needed.

Thanks!

Steve

 

[swc76801]

Hi Steve,

So, is one of the Dlookup method works? Which one?

There's very little information why you need to record "Rate" into the
"Customers" table.

It's a good idea to track changes (Rate) in the "Customers" Table only if the
Rate is always changing. Example, you want to find out what was the "Rate"
for the customer for last year. But of course, there are other methods
without the Column "Rate" in the "Customers" Table.

Having the column "Rate" in "Customer" table will be easier for a beginner.

We'll need more input on how, where, etc. Form design.

My guess for such action is to use a combobox to autoFill the "Rate" on your
form but with very little details there's not much we can go about.

Something like say....

Me.Rate = Me.ComboboxName.Column(1)

More likely we will use a combobox which is bound the "CustomerID" and in the
combo's Rowsource, something like "SELECT CustomerDetail.CustomerID, Rooms.
Rate FROM CustomerDetail, Rooms"

I'm assuming there is a Table called "CustomerDetail" which is the list of
all your customer information.

If your combobox is name "CustomerID" and the field/Textbox is name "Rate"
We'll have something like...

Me.Rate = Me.CustomerID.Column(1)

You put this into the combobox afterupdate event.

The "Column(1)" the the combobox rowsource query, column(0) is the CustomerID
and Column(1) is the "Rate"

Hope to get you something to start with.

SWC76801 wrote:
On Apr 18, 12:58 am, "AccessVandal via AccessMonster.com" <u18947@uwe>
wrote:
Geezz!
[quoted text clipped - 9 lines]
--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200704/1
My ISP was down last night, so I couldn't reply to the various
messages. First of all, based on suggestions I received, I changed the
name for the table from Rate to Rooms. The table only contains the
RoomID, Room Name and Rate. I would very much like to be able to store
the data for Rate and not just display it, however; I haven't been
able to find the correct procedure to do that.
I've also tried both of the options provided by AccessVandal but
neither of them work in the form. As with other attempts, when I test
them with Ctrl+G they work as they should, but when used in the form,
they will not work. I'm obviously doing something really stupid here
that prevents them form working.

- Show quoted text -

Sorry that I didn't explain the version that was working better. What
happened was that I had the Dlookup function working on a table that I
deleted. The logic I was using to generate the table was faulty so I
deleted the table and built a new one. From then on I haven't been
able to get the function to work properly. Now I realize that it
wasn't the function I needed. I'm now tyring to learn how to do the
combobox. This definitely seems to be what I really needed.

Thanks!

Steve- Hide quoted text -

- Show quoted text -

The combobox worked perfectly! It does exactly what I wanted to do and
is much easier than the Dlookup! This is good.

Now, I'm running into another problem. Now that I have the value for
"Rate" I have calculated the time for how long the room is being used.
I then multiply "Rate" by ""Time" to get "Charges". There is a field
in the Reservations table named "Charges". However; even though I have
successfully been able to calculate the value for charges and display
it in the form, the data isn't being stored in the Reservations table.
I'm not sure what I'm missing here.

I've also built an IF/Then statement that is based on a "yes/no"
field. I've not been able to find what value I need to use for a the
"yes" or "true" value. I've been able to find out that it is a one
byte value, but I'm not having much luck with this. I've tested the IF/
Then function using fields that I know the stored value and it works
perfectly. I'm sure this is very basic info, but I can't seem to find
it.

 

[John W. Vinson]

Now, I'm running into another problem. Now that I have the value for
"Rate" I have calculated the time for how long the room is being used.
I then multiply "Rate" by ""Time" to get "Charges". There is a field
in the Reservations table named "Charges". However; even though I have
successfully been able to calculate the value for charges and display
it in the form, the data isn't being stored in the Reservations table.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]

 

[SWC76801]

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]

Thanks for helping clear this up. After reading your explanation, it
makes perfect sense. Just shows me how much I have to learn!

 

[AccessVandal via AccessMonster.com]

Hi Steve,

Now, I'm running into another problem. Now that I have the value for
"Rate" I have calculated the time for how long the room is being used.
I then multiply "Rate" by ""Time" to get "Charges". There is a field
in the Reservations table named "Charges". However; even though I have
successfully been able to calculate the value for charges and display
it in the form, the data isn't being stored in the Reservations table.
I'm not sure what I'm missing here.

Read, John’s message.

I've also built an IF/Then statement that is based on a "yes/no"
field. I've not been able to find what value I need to use for a the
"yes" or "true" value. I've been able to find out that it is a one
byte value, but I'm not having much luck with this. I've tested the IF/
Then function using fields that I know the stored value and it works
perfectly. I'm sure this is very basic info, but I can't seem to find
it.

The value of “yes/no†is “-1†for True(yes) and “0†for False(no). Is that
what you were looking for?

 

[swc76801]

Hi Steve,


Read, John's message.


The value of "yes/no" is "-1" for True(yes) and "0" for False(no). Is that
what you were looking for?

That's exactly what I needed! I knew it had to be a simple answer, but
just couldn't find it. This works perfect now!

Thanks again!

Steve