adding records with a button 1

[etcrier]

I have 3 tables in access with NO SQL just an access DB
1 = customer table
2 = generator table
3 = location table
"2" & "3" relate to ID of "1".

there can be many 3s to one 2 and many 2s to one 1.
I have all three on a form.
when I add a "1" record I want to hit a button that will make a default "2" and "3" records. because 80% of the file has only 1-3, 1-2, 1-1 for a customer, Only 20% have mutiple 2s & 3s.
some fields in "2" and "3" can be defaulted from date in "1" and changed later if information turns out to be different.

I guess I need to know how to do an add for a teble rec in VB from a button click ?

 

[Remou]

An Access DB has Jet SQL. You can create an append query that can be run from a button or you can build and SQL string that can be used in code. I recommend starting with an Append query.

 

[etcrier]

Can you give me an eample of the jet sql code to add a record for this example
table "abs"
fielda="123"
fieldb="567"
fieldc= tableG[field7]

I feel if I can get the basic format tied to real data i can take it from there.

I have added VB code on a button before.
I am an ol "c" coder SQL & VB are very new to me but i understand it all once I see an example using real fields
thanks

your a great help

 

[JBinQLD]

SQL.

INSERT INTO abs (fielda, fieldb, fieldc)
SELECT "123" As a, "567" AS b, [tableg].[field7]
FROM tableg;


For VBA:

dim db as database
dim rs as recordset
set db=currentdb
set rs=db.openrecordset("abs".dbopendynaset)

rs.addnew
rs!fielda="123"
rs!fieldb="567"
rs!fieldc=dlookup(field7,"tableg")
rs.update
rs.close

set rs=nothing
set db=nothing

Happy Dayz

 

[JBinQLD]

PS. To run a query, or SQL from command button:

Docmd.openquery QueryName

or

Docmd.runSQL ("SELECT ...blah de blah;")

 

[Remou]

I would use:

Dim db As Database
Set db=CurrentDB
strSQL="INSERT INTO abs (fielda, fieldb, fieldc) SELECT '123','567',Field7  FROM tableg"
db.Execute strSQL, dbFailOnError
MsgBox "You have added " & db.RecordsAffected

I find it is better to separate the SQL string out, it makes it easier to debug syntax errors. While I find the query window is a useful guide, some people would say it is poison and should never be used because the SQL it produces is limited, ugly and buggy. Microsoft has produced three articles that you may find useful*. The key word SELECT is used when data is to be inserted from a table, VALUE is used if only values are to be inserted, for example:

strSQL="INSERT INTO abs (fieldx, fieldy, fieldz) " _
& "VALUES ( '" _
& Replace(strTextData,"'","''") & "'," _
& lngNumericData & ",#" _
& Format(Date,"yyyy/mm/dd") & "# )"

Note the delimiters for the various field types (text, numeric, datetime). Note also that replacing single quotes (') with two single quotes ('') in strings will eliminate a possible error and that dates must be unambiguous, hence formatting to yyyy/mm/dd is often used.

Using db.Execute allows you to get the number of records affected and also avoids the default Access warning.

'====================================
* Fundamental Microsoft Jet SQL for Access 2000 (includes download AcFundSQL.exe) :

http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

Intermediate Microsoft Jet SQL for Access 2000 (includes download AcIntSQL.exe):

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Advanced Microsoft Jet SQL for Access 2000 (includes download AcAdvSQL.exe):

http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx

 

[JBinQLD]

Nice hints Mr Remou!

A bit "off-course", but in your experience, when you have a rather complex query is there a downside to saving the query and performing a

docmd.openquery "ComplexInsertQuery"

rather than

db.execute strComplexInsertSQL?

or even via VBA?

 

[Remou]

I would generally think that I have done something wrong in the design if an action query was complex. Select queries can be a little intricate, but I generally find that action queries are simple enough, although it may be necessary to run several in a transaction. I would not generally have saved queries, because I prefer to store the SQL in a table. These days, I am enjoying parameters. I think it is some years since I 'opened' an action query.

Here are some comments:

http://www.eggheadcafe.com/software/aspnet/31915744/qry-vs-sql-code.aspx

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=84&Number=1623302&fpart=1