DLookup Problems

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200704/1

On Apr 19, 2:22 am, "AccessVandal via AccessMonster.com" u18947@uwe
wrote:
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.

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200704/1- Hide quoted text -

- 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

On Apr 23, 2:14 pm, wrote:
On Apr 19, 2:22 am, "AccessVandal via AccessMonster.com" u18947@uwe
wrote:





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.

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200704/1-Hide quoted text -

- 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.

On 23 Apr 2007 14:22:10 -0700, wrote:

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]

On Apr 23, 7:33 pm, John W. Vinson
wrote:
On 23 Apr 2007 14:22:10 -0700, wrote:

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]

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

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?

wrote:
On Apr 23, 2:14 pm, wrote:

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200704/1

On Apr 24, 5:12 am, "AccessVandal via AccessMonster.com" u18947@uwe
wrote:
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?

wrote:
On Apr 23, 2:14 pm, wrote:

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200704/1

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