By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,639 Members | 2,262 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,639 IT Pros & Developers. It's quick & easy.

Before Insert Event on Subform

P: n/a
I'm having what seems to me to be an odd problem. Perhaps there is some
explanation, but don't know at this point. Basically I have a form that
tracks memberships and donations. The main form tracks the individual and
the subform allows me to add donation amounts or membership fee payments.
It's fairly basic. Well what I want to do is when I enter a new membership
payment it looks to another table. If the person is currently a member, it
does nothing. If the person is an ex-member then it runs an update
statement and changes it to member and if the person has never been a member
it runs an insert statement to add them to the table.

Below is the problem code. The problem comes into play when I'm trying to
evaluate the subform combo box which is what holds the id for what type of
payment it is, whether it be a donation or membership. It runs code based
on its value. It wasn't doing what I wanted so I inserted a debug line to
determine what the combo box was evaluating to. Look at the code below
where there is [problem][/problem]. If I leave it like that I cannot edit
the record in the subform. If I comment out that part I can edit it as much
as my heart desires. WHY?? If I put it into an AfterInsert it allows me to
edit and such but it's pointless because the combobox will evaluate to null
because it's at a new record already. Should I put this in a different
event?

Thanks!

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLInsert As String
Dim strSQLUpdate As String

strSQL = "SELECT idIndividualType FROM tblxrefPatronType"
strSQL = strSQL & " WHERE idIndividual=" &
Forms!frmPatronInfo!txtidIndividual
strSQL = strSQL & " AND idIndividualType IN (1,2)"
Debug.Print "cboidIncomeType: " [problem]&
Forms!frmIncomeType!cboidIncomeType[/problem]
Debug.Print "strSQL1: " & strSQL
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
well the sql code should be:

strSQL = "SELECT idIndividualType FROM tblxrefPatronType"
strSQL = strSQL & " WHERE idIndividual=" &
Forms!frmPatronInfo!txtidIndividual
strSQL = strSQL & " AND idIndividualType IN (1,2)"

Which shows up as such when used:
SELECT idIndividualType FROM tblxrefPatronType WHERE idIndividual=28813 AND
idIndividualType IN (1,2)

That part is all fine and good.

After playing with it a bit I'm thinking it might have something to do with
the fact that there is an autonumber field. Perhaps the insert even occurs
when I enter the first piece of data?

when i have the debug code in there like this:
Debug.Print "cboidIncomeType: " &
Forms!frmPatronInfo!frmIncomeType!cboidIncomeType
Debug.Print "strSQL1: " & strSQL

the debug.prints don't even show up in my immediate window. If I comment
out "& Forms!frmPatronInfo!frmIncomeType!cboidIncomeType" then they both
show up, but of course no value for the first line since it is commented
out. The strSQL line does come up though as it should. This is kind of the
weird situation. Why would it not even show up when it's not commented out?
The thing is is that that first line that is being debugged is the same
field I need to test to determine what do do with the data. So without an
accurate evaluation, it goes nowhere. I could put it on an on exit of one
of the fields, but I wanted it to execute on the before insert, because I
didn't want it inserted and then have someone hit the esc key but leave the
record updated in the other table.

Here's the whole of the code. I haven't put in any execute statements. I
just wanted to make sure it was running the way it should be first. Beware
word wrap.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLInsert As String
Dim strSQLUpdate As String

strSQL = "SELECT idIndividualType FROM tblxrefPatronType"
strSQL = strSQL & " WHERE idIndividual=" &
Forms!frmPatronInfo!txtidIndividual
strSQL = strSQL & " AND idIndividualType IN (1,2)"

Debug.Print "cboidIncomeType: " &
Forms!frmPatronInfo!frmIncomeType!cboidIncomeType
Debug.Print "strSQL1: " & strSQL

If Forms!frmPatronInfo!frmIncomeType!cboidIncomeType = 3 Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("strSQL")
If rs.RecordCount = 0 Then
strSQLInsert = "INSERT INTO tblxrefPatronType (idIndividual,
idIndividualType)"
strSQLInsert = strSQLInsert & " VALUES (" &
Forms!frmPatronInfo!txtidIndividual & ", 1)"
Debug.Print "strSQLInsert: " & strSQLInsert
Else
Set rs = Nothing
strSQL = "SELECT idIndividualType FROM tblxrefPatronType"
strSQL = strSQL & "WHERE idIndividual=" &
Forms!frmPatronInfo!txtidIndividual
strSQL = strSQL & " AND idIndividualType=2"
Debug.Print "strSQL2: " & strSQL
Set rs = db.OpenRecordset("strSQL")
If rs.RecordCount = 0 Then
Exit Sub
Else
strSQLUpdate = "UPDATE tblxrefPatronType SET idIndividualType=1
WHERE idIndividual="
strSQLUpdate = strSQLUpdate &
Forms!frmPatronInfo!txtidIndividual & " AND idIndividualType=1"
Debug.Print "strSQLUpdate: " & strSQLUpdate
End If
End If
End If


"Bob Quintal" <bq******@generation.net> wrote in message
news:ca******************************@news.teranew s.com...
Your SQL seems to be missing a portion of one of the filter
conditions.

" WHERE idIndividual=" & Forms!frmPatronInfo!txtidIndividual _
& " AND idIndividualType IN (1,2)"
Is okay,

but taking out the debugging code leaves
& Forms!frmIncomeType!cboidIncomeType
which should be something like " And [whatever] = " &
Forms!frmIncomeType!cboidIncomeType

It may be a cut & paste error, but please repost the code.

Bob Q

"Bill" <zi*****@hotmail.com> wrote in
news:vm************@corp.supernews.com:
I'm having what seems to me to be an odd problem. Perhaps
there is some explanation, but don't know at this point.
Basically I have a form that tracks memberships and donations.
The main form tracks the individual and the subform allows me
to add donation amounts or membership fee payments. It's
fairly basic. Well what I want to do is when I enter a new
membership payment it looks to another table. If the person
is currently a member, it does nothing. If the person is an
ex-member then it runs an update statement and changes it to
member and if the person has never been a member it runs an
insert statement to add them to the table.

Below is the problem code. The problem comes into play when
I'm trying to evaluate the subform combo box which is what
holds the id for what type of payment it is, whether it be a
donation or membership. It runs code based on its value. It
wasn't doing what I wanted so I inserted a debug line to
determine what the combo box was evaluating to. Look at the
code below where there is [problem][/problem]. If I leave it
like that I cannot edit the record in the subform. If I
comment out that part I can edit it as much as my heart
desires. WHY?? If I put it into an AfterInsert it allows me
to edit and such but it's pointless because the combobox will
evaluate to null because it's at a new record already. Should
I put this in a different event?

Thanks!

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLInsert As String
Dim strSQLUpdate As String

strSQL = "SELECT idIndividualType FROM tblxrefPatronType"
strSQL = strSQL & " WHERE idIndividual=" &
Forms!frmPatronInfo!txtidIndividual
strSQL = strSQL & " AND idIndividualType IN (1,2)"
Debug.Print "cboidIncomeType: " [problem]&
Forms!frmIncomeType!cboidIncomeType[/problem]
Debug.Print "strSQL1: " & strSQL

Nov 12 '05 #2

P: n/a
Well there are about a dozen other flaws in my code, so I'm going to fix
those first and see if that works. If not I'll probably repost or figure
something else. Why do these things have to be so literal? :)
"Bob Quintal" <bq******@generation.net> wrote in message
news:ca******************************@news.teranew s.com...
Your SQL seems to be missing a portion of one of the filter
conditions.

" WHERE idIndividual=" & Forms!frmPatronInfo!txtidIndividual _
& " AND idIndividualType IN (1,2)"
Is okay,

but taking out the debugging code leaves
& Forms!frmIncomeType!cboidIncomeType
which should be something like " And [whatever] = " &
Forms!frmIncomeType!cboidIncomeType

It may be a cut & paste error, but please repost the code.

Bob Q

"Bill" <zi*****@hotmail.com> wrote in
news:vm************@corp.supernews.com:
I'm having what seems to me to be an odd problem. Perhaps
there is some explanation, but don't know at this point.
Basically I have a form that tracks memberships and donations.
The main form tracks the individual and the subform allows me
to add donation amounts or membership fee payments. It's
fairly basic. Well what I want to do is when I enter a new
membership payment it looks to another table. If the person
is currently a member, it does nothing. If the person is an
ex-member then it runs an update statement and changes it to
member and if the person has never been a member it runs an
insert statement to add them to the table.

Below is the problem code. The problem comes into play when
I'm trying to evaluate the subform combo box which is what
holds the id for what type of payment it is, whether it be a
donation or membership. It runs code based on its value. It
wasn't doing what I wanted so I inserted a debug line to
determine what the combo box was evaluating to. Look at the
code below where there is [problem][/problem]. If I leave it
like that I cannot edit the record in the subform. If I
comment out that part I can edit it as much as my heart
desires. WHY?? If I put it into an AfterInsert it allows me
to edit and such but it's pointless because the combobox will
evaluate to null because it's at a new record already. Should
I put this in a different event?

Thanks!

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLInsert As String
Dim strSQLUpdate As String

strSQL = "SELECT idIndividualType FROM tblxrefPatronType"
strSQL = strSQL & " WHERE idIndividual=" &
Forms!frmPatronInfo!txtidIndividual
strSQL = strSQL & " AND idIndividualType IN (1,2)"
Debug.Print "cboidIncomeType: " [problem]&
Forms!frmIncomeType!cboidIncomeType[/problem]
Debug.Print "strSQL1: " & strSQL

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.