Add record to another table

  • Thread starter Thread starter Sgwapt
  • Start date Start date
S

Sgwapt

What should I do in this case?

When user is prompted "Do you want to add to inventory?"
and selects "Yes" I want values on user form and auto values to be added to
another table. In this case the other table is "Inventory" The user form is
name "Inspection Entry".

This is what i have so far but cant get the open table or to work.

With rstTable
.AddNew
.Fields("TransType") = "FINISHEDGOODS"
.Fields("CreateDate") = Date
.Fields("ModDate") = Date
.Fields("ClientID") = TxtCustID.Value
.Fields("ClientItem") = TxtPartNo.Value
.Fields("Amount") = TxtQuantity.Value
.Fields("Comments") = TxtComments.Value
.Update
End With

Thank you in advance
 
Sqwapt,

Try this code:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Inventory")
rs.AddNew
rs.Fields("TransType") = "FINISHEDGOODS"
rs.Fields("CreateDate") = Date
rs.Fields("ModDate") = Date
rs.Fields("ClientID") = TxtCustID.Value
rs.Fields("ClientItem") = TxtPartNo.Value
rs.Fields("Amount") = TxtQuantity.Value
rs.Fields("Comments") = TxtComments.Value
rs.Update
rs.Close
Set rs = Nothing

For this code to work you should have a reference to the "Microsoft DAO 3.6
Object Library. Go to Tools/References and if there is not a check marke
next to a reference to this library there then scroll down, find it and check
it.

HTH
Mr. B
askdoctoraccess dot com
 
Thank you Mr B.

That was just the thing I needed.
I knew it had to do with Recordset just could not get past the syntax.

I can dance in Excel VBA but still learning Access VBA

Thank you again Doctor
 
You might want to check out action queries as well.

There's nothing particularly wrong with using a recordset, they're solid and
won't fail you (for any normal reason anyway), but queries seem to be the
perferred way to handle something like this.

I bring this up because I'm just coming to this realization after a few
years of coding, really wishing that I had paid more attention to them from
the beginning so I'm not wondering when I'll ever have the time to go back
through and convert my code.

They use two different services to get the data, and queries are set up to
automatically opening the table and finding the appropriate records, etc,
while with vba you need to 'manually' do it with a service designed to do
many other things, instead of just manipulating data. Much quicker and
cleaner.

As I said, there is nothing wrong with using a recordset (they work great
for everything I do), but a query is really the way to go, and its basically
a two-line procedure: build the sql string, and execute it.

So if you're a 'tinkerer' don't bother worrying about it, but if you want to
save a few hours down the long road, I would advise giving them a look.

If you google "mvp strive4peace" or "allen browne tips" the top links should
get you to their tutorial/tips pages where you should be able to find
everything you need for a good understanding of them. I expect "access
action queries" would return something useful as well.

happy coding!

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill
 
Back
Top