How do I .....

[RiceFarmer]

I am designing a farm records database in Access 2007. I have a weight
ticket form that is bound to a weight ticket table. On the form is a combo
box where I select a buyer. Once a buyer is selected, I need to load the
buyer's discount schedule in order to calculate deductions for the weight
ticket. The discounts vary with moisture content, test weight, etc., and
each buyer may have a different discount schedule. I don't want to store the
calculations in the table, so I must calculate the results on the form. How
do i get the discount schedule data out of the table and use it each time I
change buyers/weight tickets? I'm looking for the concept, not necessarily
someone to write the code for me. I must have every Access book available,
but can't seem to locate info to help. I appreciate any help! Thanks!

 

[bhicks11 via AccessMonster.com]

Sounds like a job for DLOOKUP. You set the value of an unbound control
through DLOOKUP to look up. DLOOKUP can look up something in a seperate
table by passing the appropriate parameters (like customer ID). Then you can
run your calculation based on that unbound control's value.

Example:

Me.OFFICEORG = DLookup("[OFFICEORG]", "[TEST]", "([NUM]= '" & Me.NUM & "')
And (

• = '" & Me.List & "')")
  
  Bonnie
  http://www.dataplus-svc.com

 

[RiceFarmer via AccessMonster.com]

Sounds like a job for DLOOKUP. You set the value of an unbound control
through DLOOKUP to look up. DLOOKUP can look up something in a seperate
table by passing the appropriate parameters (like customer ID). Then you can
run your calculation based on that unbound control's value.

Example:

Me.OFFICEORG = DLookup("[OFFICEORG]", "[TEST]", "([NUM]= '" & Me.NUM & "')
And (

• = '" & Me.List & "')")
  
  Bonnie
  http://www.dataplus-svc.com

• 
  Thanks for the reply, and sorry for somehow posting in 2 places. What if i
  need several different numbers for one buyer from the same record? Do I just
  do a series of Dlookup's, or is there a way to pull a whole record from a
  table at one time and store the values in variables to use in the several
  calculations that take place to finish the ticket?

  I am designing a farm records database in Access 2007. I have a weight
  ticket form that is bound to a weight ticket table. On the form is a combo

  [quoted text clipped - 7 lines]

  someone to write the code for me. I must have every Access book available,
  but can't seem to locate info to help. I appreciate any help! Thanks!

 

[bhicks11 via AccessMonster.com]

I would just use as many DLookup's as needed to get what you need. Sometimes
I populate a form based on a unique ID number with Dlookups. Yes, you can
use memory variables (Dim, Public or Private) in the VBA or controls
depending on your usage.

Bonnie
http://www.dataplus-svc.com

Sounds like a job for DLOOKUP. You set the value of an unbound control
through DLOOKUP to look up. DLOOKUP can look up something in a seperate

[quoted text clipped - 8 lines]

Bonnie
http://www.dataplus-svc.com

Thanks for the reply, and sorry for somehow posting in 2 places. What if i
need several different numbers for one buyer from the same record? Do I just
do a series of Dlookup's, or is there a way to pull a whole record from a
table at one time and store the values in variables to use in the several
calculations that take place to finish the ticket?

I am designing a farm records database in Access 2007. I have a weight
ticket form that is bound to a weight ticket table. On the form is a combo

[quoted text clipped - 7 lines]

someone to write the code for me. I must have every Access book available,
but can't seem to locate info to help. I appreciate any help! Thanks!

 

[BruceM]

You said:
"The discounts vary with moisture content, test weight, etc., and
each buyer may have a different discount schedule"

I'm not familiar with your business or the specifics of your design, so I
need to ask the source of the moisture content, test weight, etc. data, and
whether the discount schedule is stored in a table related to the Buyer
record. While a series of DLookups may be one approach, there is a lot of
computing overhead with domain functions (DLookup, DSum, etc.). It could
bog down your database. Related records may be a more direct route.

Sounds like a job for DLOOKUP. You set the value of an unbound control
through DLOOKUP to look up. DLOOKUP can look up something in a seperate
table by passing the appropriate parameters (like customer ID). Then you
can
run your calculation based on that unbound control's value.

Example:

Me.OFFICEORG = DLookup("[OFFICEORG]", "[TEST]", "([NUM]= '" & Me.NUM & "')
And (

• = '" & Me.List & "')")
  
  Bonnie
  http://www.dataplus-svc.com

• 
  Thanks for the reply, and sorry for somehow posting in 2 places. What if
  i
  need several different numbers for one buyer from the same record? Do I
  just
  do a series of Dlookup's, or is there a way to pull a whole record from a
  table at one time and store the values in variables to use in the several
  calculations that take place to finish the ticket?

  I am designing a farm records database in Access 2007. I have a weight
  ticket form that is bound to a weight ticket table. On the form is a
  combo

  [quoted text clipped - 7 lines]

  someone to write the code for me. I must have every Access book
  available,
  but can't seem to locate info to help. I appreciate any help! Thanks!

 

[RiceFarmer via AccessMonster.com]

Thanks for the help. I'll give it a try.

I would just use as many DLookup's as needed to get what you need. Sometimes
I populate a form based on a unique ID number with Dlookups. Yes, you can
use memory variables (Dim, Public or Private) in the VBA or controls
depending on your usage.

Bonnie
http://www.dataplus-svc.com

Sounds like a job for DLOOKUP. You set the value of an unbound control
through DLOOKUP to look up. DLOOKUP can look up something in a seperate [quoted text clipped - 12 lines]
someone to write the code for me. I must have every Access book available,
but can't seem to locate info to help. I appreciate any help! Thanks!

 

[bhicks11 via AccessMonster.com]

Hi Bruce, I was curious because I use Dlookups and haven't noticed any
degradation. I found this test that shows the time differences:

Number of Records: 520378

DLookup100 iterations) CAZN / 0.047 seconds

DAO 100 iterations) CAZN / 0.031 seconds

ADO 100 iterations) CAZN / 0.141 seconds

I think it's negligible unless you are doing beaucoup beaucoups. But I think
our friend is investigating options and concepts so good info all around.

Bonnie
http://www.dataplus-svc.com

You said:
"The discounts vary with moisture content, test weight, etc., and
each buyer may have a different discount schedule"

I'm not familiar with your business or the specifics of your design, so I
need to ask the source of the moisture content, test weight, etc. data, and
whether the discount schedule is stored in a table related to the Buyer
record. While a series of DLookups may be one approach, there is a lot of
computing overhead with domain functions (DLookup, DSum, etc.). It could
bog down your database. Related records may be a more direct route.

Sounds like a job for DLOOKUP. You set the value of an unbound control
through DLOOKUP to look up. DLOOKUP can look up something in a seperate [quoted text clipped - 24 lines]
available,
but can't seem to locate info to help. I appreciate any help! Thanks!

 

[BruceM]

I guess my thinking was that if there are related records the easiest way to
show them is by taking advantage of the relationship rather than with an
added expression. The overhead problem with the domain functions may become
evident when the calculation is being performed in a calculated field, and
therefore repeated for each record, but I really don't know the details, as
I haven't had to deal with really large numbers of records.

Having said all that, I agree that a review of the various options is a good
thing.

Hi Bruce, I was curious because I use Dlookups and haven't noticed any
degradation. I found this test that shows the time differences:

Number of Records: 520378

DLookup100 iterations) CAZN / 0.047 seconds

DAO 100 iterations) CAZN / 0.031 seconds

ADO 100 iterations) CAZN / 0.141 seconds

I think it's negligible unless you are doing beaucoup beaucoups. But I
think
our friend is investigating options and concepts so good info all around.

Bonnie
http://www.dataplus-svc.com

You said:
"The discounts vary with moisture content, test weight, etc., and
each buyer may have a different discount schedule"

I'm not familiar with your business or the specifics of your design, so I
need to ask the source of the moisture content, test weight, etc. data,
and
whether the discount schedule is stored in a table related to the Buyer
record. While a series of DLookups may be one approach, there is a lot of
computing overhead with domain functions (DLookup, DSum, etc.). It could
bog down your database. Related records may be a more direct route.

Sounds like a job for DLOOKUP. You set the value of an unbound control
through DLOOKUP to look up. DLOOKUP can look up something in a seperate

[quoted text clipped - 24 lines]

available,
but can't seem to locate info to help. I appreciate any help! Thanks!

 

[bhicks11 via AccessMonster.com]

Yes, I do the DLookup when it is an unrelated table. I also use a query with
related tables when more feasible.

Good-O Bruce!

I guess my thinking was that if there are related records the easiest way to
show them is by taking advantage of the relationship rather than with an
added expression. The overhead problem with the domain functions may become
evident when the calculation is being performed in a calculated field, and
therefore repeated for each record, but I really don't know the details, as
I haven't had to deal with really large numbers of records.

Having said all that, I agree that a review of the various options is a good
thing.

Hi Bruce, I was curious because I use Dlookups and haven't noticed any
degradation. I found this test that shows the time differences:

[quoted text clipped - 31 lines]