I'm new to this - how do I add record

  • Thread starter Thread starter Flick Olmsford
  • Start date Start date
F

Flick Olmsford

I just developed a multiuser database in Access 2003. I am new to the
programming side. Currently our users are making notes on clients in a
table memo field (called Client Notes). Most of this information should be
entered in subform records, check boxes, drop down lists, and other more
reportable fields elsewhere in the db.

I am trying to move the users away from this overuse of the memo field. I
want to have the database automatically add a Client Note with text in the
memo field indicating what the user added in another table in a subform.

E.g. I have my Clients table with a linked subtable (TABLE A) - TABLE A
might store a client order or something like that.

I also have my Client Notes Table (also linked to Clients table) (TABLE B)

I want to have the database automatically add a record to table B with
information in the memo field containing the information in the new record
added to Table A.

Hope that makes sense.

I know this is redundant, but the users have been using the Client Notes
field for a LONG time and it will take a while to get them off of it. I have
a book on Access 2003 programming but it mentions making new connections to
the db. The database is already open when the code will run. Do I really
have to open a new connection?

How do I do this?
Thanks
 
I think this sounds somewhat simple. If there are users that are adding data
to a form or subform that you would like updated in your client notes table
(so they don't have to), you just need to determine which data entered you
also want added to the notes, capture that in a temptable and then either
update or append to your notes table.
 
I know the general logic, but how do I do it - what code stmts do I use. My
Access book gives some good examples, but always talks of open a connection
to the db. My database is already open. Can't I use the connection I have?

Are you talking of using a query of some sort that reads the record and
appends to the other table?

Both table A and Table B will contain permanent data.
 
I am assuming that you are using some type of form for the data to be entered.

So let's say you have someone entering values for the below.

me.lbla = CustomerName
me.lblb = CustomerOrder
me.lblc = CustomerLocation

If I am understanding you correctly, besides storing that data in your
tableA, you would like to append a record to tableB that in the memo field
reads something like:

CustomerName, CustomerOrder, CustomerLocation

That would keep people from having to go in and retype data into a "notes"
field as well as build consistency asto what is ented into the "notes" table.

Gather the fields you would like to gether and create an append field.
Depending on how many fields you want to add to the memo field could
determine whether or not you create a temp table and then append or use
variables in VB.

You can concatenate the three fields together in an append query. It would
look something like this:

strCustName = me.lbla
strCustOrder = me.lblb
strCustLoc = me.lblc

strsql = "INSERT INTO Tableb (memofield )
SELECT " & strCustName & ", " & strCustName & ", " & strCustName & " AS
Expr1 FROM tblICRIF2;"

Docmd.Runsql (strsql)

Thanks,
Roger
 
Back
Top