| re: Before Insert Event on Subform
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" <bquintal@generation.net> wrote in message
news:ca332830b3dbe2646295b4bb400c67dc@news.teranew s.com...[color=blue]
> 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" <zinck74@hotmail.com> wrote in
> news:vm6ok25nonsqe8@corp.supernews.com:
>[color=green]
> > 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
> >
> >
> >[/color]
>[/color]
| 
