Forms Post Insert Event

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating child records from a forms post insert event. Some of the data
for the new child record comes form the form and some by a query on a code
table. Is it possible to do the followinf:

1. Create a query on the code table based on the existing code value in the
main form
2. Run the query and get the data back as local variables within the post
insert procedure
3. Construct a query for crating the new child record uing data from the
form and the local variables
4. Run the sql to create the record
 
I think in this case, I would use a reocrdset. You don't mention if you need
more then one value from the code table.

I am going to assume you do need more then one value from the codes table.
(if this is wrong assumption, then I should be corrected.

And, before I lay out the code to do this, your request *instantly* hints of
data being copied when it should be relational. In other words, I look at
this request and go...oh no...coping of redundant data, this should NOT be
occurring.

If you give me a child record, then any fields and ALL data in the main
record is instantly available by the very nature of a relational database.
And, since you state that some fields in the main record will be used to
retrieve data from a code table, then again, a relational approach should be
used here (we don't actually have to copy the data, but work our way back
up, and use the relation abilities of sql to get this data).

Ok, having noted the above that could very well eliminate ALL of the need to
grab/copy your data, lets just go ahead an layout some code that would work
for your request.

dim rstChildTable as dao.RecordSet
dim rstCodeTable as dao.RecordSet
dim strSql as string

' lets retrieve the one record (all fields) from our code table.

strSql = "select * from tblcodes where codeID = " &
me!SomeCodeIdFieldInMainForm

set rstCodeTable = currentdb.openReocrdset(strSql)

' add child reocrd

set rstChildTable = currentdb.OpenRecordSet("childTable")

rstChildTable.AddNew
rstChildTable!main_ID = me!id ' this is our relational link field we
set
rstChildTAble!Field1 = rstCodeTable!SomeField
rstChildTAble!field2 = rstCodeTable!SomeOotherField
rstChildtAble!Zoo = me!zoo ' copy values from main form
rstChildTable!AnotherField = me!SomeOtherMainFormField
etc. ect.
rstChildTable.Update
rstChildTable.Close
set rstCildTable =nothing

rstCodeTable.Close
set rstCodeTable = nothing

You can see that using two reocrdsets is somewhat easier then raw sql
statements, especially since we can easily set values in the child table
that is a mix of values from the main form, and also from the codes table.

note that the above code is air code, but should give you the idea...
 
I have tried to get the code to work, but this statement gives me a 3464 Run
Time Error

Set rstCodeTable = CurrentDb.OpenRecordset(strSql)

Is there an obvious syntax error?

The complete proc code is:

Private Sub Form_AfterInsert()

' set up Child table (VolunteerActions)
Dim rstChildTable As dao.Recordset

' set up Code table (VolunteeActionCodes)
Dim rstCodeTable As dao.Recordset

' set up sql string
Dim strSql As String

' lets retrieve the one record (all fields ActionCode, Question,
Description, OnCompletion) from our code table.

strSql = "select * from VolunteerActionCodes where ActionCode = '1'"

Set rstCodeTable = CurrentDb.OpenRecordset(strSql)

' add child reocrd

Set rstChildTable = CurrentDb.OpenRecordset("VolunteerActions")

rstChildTable.AddNew
rstChildTable!ActionCode = "1"
rstChildTable!VolunteerId = Me!VolunteerId
rstChildTable!OpenClosed = False
rstChildTable!NextActionCode = rstCodeTable!OnCompletion

rstChildTable.Update
rstChildTable.Close
Set rstChildTable = Nothing

rstCodeTable.Close
Set rstCodeTable = Nothing


End Sub


Thanks for all of your help which is greatly appreciated.
 
peterg290935 said:
I have tried to get the code to work, but this statement gives me a 3464
Run
Time Error

Set rstCodeTable = CurrentDb.OpenRecordset(strSql)

Is there an obvious syntax error?
Click to expand...

Well, the question would be what does the sql look like. Put in the
following line of code right before the above

debug.print strSql

Now, after the above code runs, and fails, you can go to the immediate
window, and look at the sql you made

you can then cut/paste that sql into a new blank sql query. Just create a
new blank query, and then switch to sql view, and then paste in the sql from
your above test/debug.print.

You have got:

strSql = "select * from VolunteerActionCodes where ActionCode = '1'"

So, the debug.print code should produce:

select * from VolunteerActionCodes where ActionCode = '1'

So, try typing the above sql into a new blank query, and see what happens
(better yet, simply cut and paste the sql that appears in the
debug/immediate window for our debug.print).

Now, try and run your sql...does it run?

Perhaps ActionCode is a number type field, and not a string/text type field.
If that is the case, then you don't need quotes around the 1. This is case
where looking at your syntax will not help, and you will have to simply
check your sql here.

The rules for building sql are (I just kind of assumed that you coming from
a oracle background would have some sql skills here, and would realize how
correctly formed sql works here).

For numbers type fields in the table, you don't need quotes.
For strings/text type fields in the table, you need to surround your string
values with quotes.

Note that this rule applies to oracle, sql server, ms-access or virtually
any database engine that supports sql.

So, play around in the sql builder for a a while until you can get that sql
to work.

eg:
If actionCode is a stirng, then the sql would be

select * from VolunteerActionCodes where ActionCode = "1"

if ActionCode is a number type field, then the sql would be

select * from VolunteerActionCodes where ActionCode = 1

As I mentioned before, you *can* get away using single quotes around
strings, but you ARE supposed to use double quotes.

However, in the examples given, surrounding string values for the sql will
accept either type of quote.
 
Back
Top