Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-09-2005, 11:45 AM   #1
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
Cool Data from previous record

I have your standard customer, order, and order details database. In tblOrders, I have the the shipping address information. The reason it is in orders, a few clients can ship to different addresses and we need to track all orders and the shipping info. Here is what the boss wants. When a new order is placed for an existing customer, we need the customers previous order's shipping address to automaticly populate the new order's shipping address. I just can't seem to get it worked out in my head.

sloaner14 is offline   Reply With Quote
Old 02-09-2005, 12:15 PM   #2
llkhoutx
Newly Registered User
 
Join Date: Feb 2001
Location: Houston,Texas USA
Posts: 4,018
Thanks: 6
Thanked 106 Times in 104 Posts
llkhoutx is on a distinguished road
Lookup the last order, if any, and capture it there. That code would be virtually instantaneous using a dbsnapshot.
__________________
Get the job done right, rather than do the right job.
Do it fast or do it right. Teach a man a fish . . .
If it ain't broke, don't fix it.
Note that I don't open posted databases, period.
llkhoutx is offline   Reply With Quote
Old 02-09-2005, 01:02 PM   #3
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
Thanks for the reply. I have never done a dbsnapshot. Any hints on what to do.

sloaner14 is offline   Reply With Quote
Old 02-09-2005, 02:26 PM   #4
llkhoutx
Newly Registered User
 
Join Date: Feb 2001
Location: Houston,Texas USA
Posts: 4,018
Thanks: 6
Thanked 106 Times in 104 Posts
llkhoutx is on a distinguished road
dim db as dao.database
dim rs as dao.recordset
set db=currentdb
set rs=db.openrecordset(YourShippingTableName,dbopenSn apshot)
set db=currentdb
rs.findlast "CustomerID=" & me.CustomerID
if rs.eof then
msgbox "Error, no record found for customer")
'take appropriuate action
end if
Me.sLastAddress = rs!Address
rs.close
db.close
set rs=nothing
set db=nothing

The forgoing assumes the "last" record for the customer is the correct one. some other criteria might be involved, depending on the order of the underlying table.

A snapshot is merely a copy of the recordset. Snapshots cannot be modified; dynasets (dbopendynaset) can be.

The foregoing, at least, should get you started in the right direction.

__________________
Get the job done right, rather than do the right job.
Do it fast or do it right. Teach a man a fish . . .
If it ain't broke, don't fix it.
Note that I don't open posted databases, period.
llkhoutx is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 10:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World