useing a form to enter and update multiple records?

[Guest]

Help:::

Here is what I have:

I have a tblorders that I use for all my orders and tracking. I use this
table when I request new orders to be shipped from my factory. I can have
hundreds of records shipped on the same day. When I create my shipment
request, all the orders will have the same shipment request date within the
tblorders.

Once the orders have been shipped I receive my shipping paperwork, and
enter the shipping information for each of the orders, but I am trying to
find a way around that.

I would like to create a form of some sort that would allow me to select
a specific shipment request date from a drop down box looking up on my
tblorders. The form would also contain information such as Invoice Number,
Ship Date, Shipping Method, Shipping Cost.

Once the user enters the information on the form, all the records
containing that shipment request date would be updated with the shipping
information the user has entered...

Is this possible? and if so can anyone help me with setting it up?

Thanks in advance...

Brook

 

[Rob Oldfield]

You'd just need a form - possibly based on tblShipping (or whatever you've
called it), possibly unbound - where you enter the information. The OK
button just runs an update query that compares the date entered to the dates
on tblOrders, and updates matching records accordingly. I'd guess that the
most efficient way of doing it would be to use an AutoNumber field in
tblShipping and to update a foreign key in tblOrders with that value.

 

[Rob Oldfield]

OK. Given that you've only written one update query in the past I think
we're going to have to start small and build up. Create yourself a little
dummy database and we'll play in there before coming back to your actual
problem.

Create a table called Target. Add three fields... OrderID (autonumber and
PK, targDate (date) and targText (text). Add a few rows of data onto it.

Create a query based on that table and tell it to be an update query (from
the query menu). You're going to update the targText field with various
things.

First example will just update the field with a single value... add targText
onto the query... enter abc onto the Update To row (Access will
automatically add " marks around when you leave the box)... then hit the red
exclamation mark icon to run the query. Open the table to check the
results. Note that any text that you originally entered in targText has
been overwritten.

Next example... you can use functions to reference other fields on your
table... change the Update To row to cstr([targDate])... again hit the
exclamation icon and check the table. That should have updated targText to
be a text version of your dates. (You'll get an error if you left any of
the dates blank.)

You can also use multiple fields... change the Update To to be
cstr(dateadd("d",[OrderID],[targDate]))... and exclamation point again and
view the table. That updates the field to the targDate plus a number of
days set by the OrderID field.

Lastly (for the moment).. you can update multiple fields at the same time.
Put abc back on the Update To row for targText, add the targDate field onto
the query as well, and enter a date on the Update To row for that field.
Run it again and again look at the results.

Does that all work for you? (Apologies if you already understood all of
that... just checking that you can walk before we run...)

 

[Guest]

Hello Rob,

thank you very much for the information that you have given me, with this
along with some research that I have done I was able to figure out and learn
how to do the update that I needed to do.

Thanks again!

Brook

OK. Given that you've only written one update query in the past I think
we're going to have to start small and build up. Create yourself a little
dummy database and we'll play in there before coming back to your actual
problem.

Create a table called Target. Add three fields... OrderID (autonumber and
PK, targDate (date) and targText (text). Add a few rows of data onto it.

Create a query based on that table and tell it to be an update query (from
the query menu). You're going to update the targText field with various
things.

First example will just update the field with a single value... add targText
onto the query... enter abc onto the Update To row (Access will
automatically add " marks around when you leave the box)... then hit the red
exclamation mark icon to run the query. Open the table to check the
results. Note that any text that you originally entered in targText has
been overwritten.

Next example... you can use functions to reference other fields on your
table... change the Update To row to cstr([targDate])... again hit the
exclamation icon and check the table. That should have updated targText to
be a text version of your dates. (You'll get an error if you left any of
the dates blank.)

You can also use multiple fields... change the Update To to be
cstr(dateadd("d",[OrderID],[targDate]))... and exclamation point again and
view the table. That updates the field to the targDate plus a number of
days set by the OrderID field.

Lastly (for the moment).. you can update multiple fields at the same time.
Put abc back on the Update To row for targText, add the targDate field onto
the query as well, and enter a date on the Update To row for that field.
Run it again and again look at the results.

Does that all work for you? (Apologies if you already understood all of
that... just checking that you can walk before we run...)

Thank you very much for the reply,

This sounds like exactly what I am looking for!

However, I have ony written one update query in the past, and am unsure
how I would "compare" the date on my form to the date in my table in order to
run the update query, can you give me any suggestions?

Also, how to I send the values of the form to the values to the update
query so that it knows what to update?

Thanks,

Brook

 

[Rob Oldfield]

Excellent. Well done.

Just for reference... you're doing it by running a query containing both
your tables, a link between the date fields, and including a variable
referencing the form value? Or some other way?

Hello Rob,

thank you very much for the information that you have given me, with this
along with some research that I have done I was able to figure out and learn
how to do the update that I needed to do.

Thanks again!

Brook

OK. Given that you've only written one update query in the past I think
we're going to have to start small and build up. Create yourself a little
dummy database and we'll play in there before coming back to your actual
problem.

Create a table called Target. Add three fields... OrderID (autonumber and
PK, targDate (date) and targText (text). Add a few rows of data onto it.

Create a query based on that table and tell it to be an update query (from
the query menu). You're going to update the targText field with various
things.

First example will just update the field with a single value... add targText
onto the query... enter abc onto the Update To row (Access will
automatically add " marks around when you leave the box)... then hit the red
exclamation mark icon to run the query. Open the table to check the
results. Note that any text that you originally entered in targText has
been overwritten.

Next example... you can use functions to reference other fields on your
table... change the Update To row to cstr([targDate])... again hit the
exclamation icon and check the table. That should have updated targText to
be a text version of your dates. (You'll get an error if you left any of
the dates blank.)

You can also use multiple fields... change the Update To to be
cstr(dateadd("d",[OrderID],[targDate]))... and exclamation point again and
view the table. That updates the field to the targDate plus a number of
days set by the OrderID field.

Lastly (for the moment).. you can update multiple fields at the same time.
Put abc back on the Update To row for targText, add the targDate field onto
the query as well, and enter a date on the Update To row for that field.
Run it again and again look at the results.

Does that all work for you? (Apologies if you already understood all of
that... just checking that you can walk before we run...)

Thank you very much for the reply,

This sounds like exactly what I am looking for!

However, I have ony written one update query in the past, and am unsure
how I would "compare" the date on my form to the date in my table in

order
to

run the update query, can you give me any suggestions?

Also, how to I send the values of the form to the values to the update
query so that it knows what to update?

Thanks,

Brook

:


You'd just need a form - possibly based on tblShipping (or whatever you've
called it), possibly unbound - where you enter the information. The OK
button just runs an update query that compares the date entered to

the
dates

on tblOrders, and updates matching records accordingly. I'd guess

that
the

most efficient way of doing it would be to use an AutoNumber field in
tblShipping and to update a foreign key in tblOrders with that value.


Help:::

Here is what I have:

I have a tblorders that I use for all my orders and tracking. I use
this
table when I request new orders to be shipped from my factory. I

can
have

hundreds of records shipped on the same day. When I create my shipment
request, all the orders will have the same shipment request date within
the
tblorders.

Once the orders have been shipped I receive my shipping

paperwork,
and

enter the shipping information for each of the orders, but I am

trying
to

find a way around that.

I would like to create a form of some sort that would allow me to
select
a specific shipment request date from a drop down box looking up on my
tblorders. The form would also contain information such as Invoice Number,
Ship Date, Shipping Method, Shipping Cost.

Once the user enters the information on the form, all the records
containing that shipment request date would be updated with the shipping
information the user has entered...

Is this possible? and if so can anyone help me with setting it up?

Thanks in advance...

Brook