Access: How to append a record in a table...

[ChiknHead]

I got to questions I cant figure out (trying to learn access on my own).

(1) how do I write a record to a table that the form is not based on?

example:

I want to write the form variable [totaldue] to a table called [Sale] in the record [total]

This should be a new record in the table


(2) How to I update a field in a table not that is form is not based on *** Got this WORKING ***

example:

I pull dlookup current number of items on hand, subtract the number that was just purchased and now need to can total on hand

[qtyonhand] - [qty] = [newqty]

so I need to write [newqty] to a table called [inventory] to a field call [on hand] and of course the [itemnumber] must match so that Im updating to the correct item.


I have bought 2 access books (Im not sure they are written in English).

I would appreciate any help.

Thanks

 

[BFG10K]

You know, you would find this a lot easier if you just put everything into one table like I said before.

I want to write the form variable [totaldue] to a table called [Sale] in the record [total]

Make an append query that is based on the table "Sale" and use the criteria from the form to determine what values you want to add.

so I need to write [newqty] to a table called [inventory] to a field call [on hand] and of course the [itemnumber] must match so that Im updating to the correct item.

Do the same except use an update query.

I have bought 2 access books (Im not sure they are written in English).

LOL!

 

[ChiknHead]

I probably would be easier like you said, but I figured I better put the fields Im going to be messing with by themselves
or Im going to screw the whole database up when I "try" to update. This way I figure I can only ruin one table at a time

Ill get to reading on that now. Thanks you have saved me hours. I know what I want to do put I dont know th commands and trying to read thru the book and find what I want when I dont know what it is I want. (if that makes since)

Anyway Ill give that a shot now...

Thanks Again

 

[BFG10K]

or Im going to screw the whole database up when I "try" to update. This way I figure I can only ruin one table at a time

If you put everything into one table you'll have less chance of screwing things up since you'll be able to directly control what fields will get changed by what you put on the form. The way you're currently doing it has the potential to be more dangerous as you're going to have to run queries that go off and do their own thing.

I know what I want to do put I dont know th commands and trying to read thru the book and find what I want when I dont know what it is I want. (if that makes since)

If you're still going to use queries then you can use the event properties of the form (eg On Update) to trigger when the queries run. Open the form, get the properties of it, click on the "Event" tab and you'll see them all there.

 

[ChiknHead]

Ok I made the append query and go it to execute but it says "append 0 records" just great..

In the code right before it does the docmd to open the query do I set the table field values (that Im appending) to the ones on the form?

If so, what would the code look like (table = [Cash] and field = [Amount]) form variable is [TotalDue]

I think Im going to call it a night...

BTW I got the Updating query to work. (Thanks for the Help)

Thanks for the help you've given me...probably saved my laptop screen

Microsoft - Running Microsoft Access - $44.99
Osborne - The Complete Reference - Access - $39.99

still having to go online and beg for help - priceless

 

[ChiknHead]

argh...

 

[BFG10K]

If the query is appending 0 records then it's because the criteria you've put into it doesn't match any of the records in the table/query you're looking at.

In the code right before it does the docmd to open the query do I set the table field values (that Im appending) to the ones on the form?

No, your append query is the one that should be adding each of the fields. Your query needs to use the values from the form and append those to the other table.

 

[ChiknHead]

You da man....

I think thats got it now.

Is there anyway to bypass the the popup box "You are about to append"...So it will just run the query and not
ask you that?

Thanks again

 

[BFG10K]

DoCmd.SetWarnings (false)

 

[Evadman]

Originally posted by: BFG10K
DoCmd.SetWarnings (false)

Do not forget to turn it back to false when your code is done running or it will stay as false until access is restarted.