In VBA how do I put data into a table?

[Dave]

SO I click a button on a form and it takes the data in a field on that form
and puts it in a table, not related to that form.


Thanks

 

[Jeff Boyce]

Dave

Is your form completely unbound, or is it bound to a different table?

If you already are storing the data in one table, why are you trying to
store it in another?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dave]

It is bound to a different object.
I am trying to stamp a date an order is placed in the Customer Table (upon
closing (well printing) the Order Form, so that I can filter the customer
list (at open) to only customers that have place an order since that date.

make since?

Thanks

 

[Jeff Boyce]

Dave

I can't be sure, but it sounds like your data structure would allow a query
to determine that, rather than having to redundantly store the date
information. One of the significant issues with redundant data is that one
could be (independently) altered without affecting the other ... and which
one is correct?!

Will a query joining your related tables work to get you that information?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dave]

Sometimes my explanation of my needs does not convey enough for anyone to
help
My apologies.
I think this is probably really easy - if I could just explain myself
better.


"tblCustomers"
has all my customer info plus a field for "LastOrderDate" (Meaning the Last
time they placed an order)

"refLastOrderDate"
is a single row table where the admin can limit how long a customer list is
getting by entering a date that he wishes to have no customers listed who
have not ordered since that time

my query to open the form "frmCustomerList " filters great based upon the
above 2 objects

HOWEVER there is currently no way to enter the "LastOrderDate" in
"tblCustomers"

"tblOrders" has a field called "OrderDate" which when an order form
("frmOrders")opens is populated with todays date.

When I close (Print Preview) "frmOrders" I want that OrderDate (Todays date)
to populate the field "LastOrderDate" in "tblCustomers" FOR THAT CUSTOMER
so that the filter will work.

I hope that is more clear on my needs

Thanks for your patience
Dave

 

[Bob Quintal]

Sometimes my explanation of my needs does not convey enough
for anyone to help
My apologies.
I think this is probably really easy - if I could just explain
myself better.


"tblCustomers"
has all my customer info plus a field for "LastOrderDate"
(Meaning the Last
time they placed an order)

"refLastOrderDate"
is a single row table where the admin can limit how long a
customer list is
getting by entering a date that he wishes to have no customers
listed who have not ordered since that time

my query to open the form "frmCustomerList " filters great
based upon the above 2 objects

HOWEVER there is currently no way to enter the "LastOrderDate"
in "tblCustomers"

"tblOrders" has a field called "OrderDate" which when an order
form ("frmOrders")opens is populated with todays date.

When I close (Print Preview) "frmOrders" I want that OrderDate
(Todays date) to populate the field "LastOrderDate" in
"tblCustomers" FOR THAT CUSTOMER so that the filter will work.

I hope that is more clear on my needs

Thanks for your patience
Dave

It is clear what you want for the final result, Here is a
different way to obtain the list of Current customers, that does
not need the last order date as a field in the table. The query
uses a calculated field

LastOrderDate: (SELECT max(OrderDate FROM Orders WHERE
Orders.CustomerID = Customers.CustomerID)

Put your filter in the Criteria row for this calculated field.

 

[Dave]

I got it solved with an Update query in the code.
Seems to do exactly what I want - don't know ifit was the best way but it
does work.

Thanks four your replies.
Dave

 

[Dave]

Bob,
Thanks for the reply.
This approach looks like it might solve some other issues I am having with
this
but I am not sure how/where you want me to use it.

dave

 

[Bob Quintal]

Bob,
Thanks for the reply.
This approach looks like it might solve some other issues I am
having with this
but I am not sure how/where you want me to use it.

dave

Here's a little more detail. Open the query you said you use as
the report source. Bring down the fields you want but instead of
bringing down the LastOrderDate, you place the following
expression in the field name box
LastOrderDate: (SELECT max(OrderDate) FROM Orders WHERE
Orders.CustomerID = Customers.CustomerID)

That sets up a subquery that gets the last order date for that
customer and puts it into the row.

HTH

It is clear what you want for the final result, Here is a
different way to obtain the list of Current customers, that
does not need the last order date as a field in the table.
The query uses a calculated field

LastOrderDate: (SELECT max(OrderDate FROM Orders WHERE
Orders.CustomerID = Customers.CustomerID)

Put your filter in the Criteria row for this calculated
field.

 

[Dave]

Bob,

Took me a few trys to get it dialed in but it is working now.

Thanks so much
Dave

Bob,
Thanks for the reply.
This approach looks like it might solve some other issues I am
having with this
but I am not sure how/where you want me to use it.

dave

Here's a little more detail. Open the query you said you use as
the report source. Bring down the fields you want but instead of
bringing down the LastOrderDate, you place the following
expression in the field name box
LastOrderDate: (SELECT max(OrderDate) FROM Orders WHERE
Orders.CustomerID = Customers.CustomerID)

That sets up a subquery that gets the last order date for that
customer and puts it into the row.

HTH

 

[Jeff Boyce]

Dave

I'm wondering, especially in light of your responses to Bob's information,
why you might still be wanting to add a redundant LastDate value (or so it
seems)?

Being able to do it (e.g., with an Update query) doesn't mean it's
necessarily a good idea to do it...

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dave]

Jeff,
Well it is possable I am STILL missundrstanding something (hell -
likely)
but I think with Bob's help (and others here) I have elimited the redundant
value (I think).

I have removed the "LastOrderDate" field from "tblCustomers.
I now use what bob has provided in my query that calls the form
"frmCustomerList"
It is a calculated field based upon bob's code and a criteria I already had
in place.
all coded Update quiers have been removed.

I think this is the direction everyone was trying to guide me to - I was
just a little slow on getting the resolution.

Does this approach sound right?

Thanks
Dave

 

[Jeff Boyce]

If that "calculated field" is in the query, not in a table, then yes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dave]

Yep - in the query now - not in a table.

Thanks

If that "calculated field" is in the query, not in a table, then yes.

Regards

Jeff Boyce
Microsoft Office/Access MVP