what is wrong with this code?

  • Thread starter Thread starter hermie
  • Start date Start date
H

hermie

In the field [ss] on my data entry form I put code in the afterUpdate
field. The code runs but not update the field in table notas. First I get
the message: You are about to run an append query that will modify data in
your table. when I click OK a second message appears which says: You are
about to append 0 row(s) What should be 1 row

What is wrong with the code?

Private Sub ss_AfterUpdate()
DoCmd.OpenQuery ("Students_Without_Matching_Notas")
Run
DoCmd.Close ("Students_Without_Matching_Notas")
DoCmd.OpenQuery ("addssnota")
End Sub

any help is welcome

Herman
 
Yes when i run the queries all works fine

This code works for multiple entries only the last is not appended.
So for a single add of an SS it will not append

Herman

tina said:
does the append occurr if you run the query manually instead of from code?


hermie said:
In the field [ss] on my data entry form I put code in the afterUpdate
field. The code runs but not update the field in table notas. First I get
the message: You are about to run an append query that will modify data in
your table. when I click OK a second message appears which says: You are
about to append 0 row(s) What should be 1 row

What is wrong with the code?

Private Sub ss_AfterUpdate()
DoCmd.OpenQuery ("Students_Without_Matching_Notas")
Run
DoCmd.Close ("Students_Without_Matching_Notas")
DoCmd.OpenQuery ("addssnota")
End Sub

any help is welcome

Herman
Click to expand...
Click to expand...
 
hermie said:
In the field [ss] on my data entry form I put code in the afterUpdate
field. The code runs but not update the field in table notas. First
I get the message: You are about to run an append query that will
modify data in your table. when I click OK a second message appears
which says: You are about to append 0 row(s) What should be 1 row

What is wrong with the code?

Private Sub ss_AfterUpdate()
DoCmd.OpenQuery ("Students_Without_Matching_Notas")
Run
DoCmd.Close ("Students_Without_Matching_Notas")
DoCmd.OpenQuery ("addssnota")
End Sub

any help is welcome

Herman
Click to expand...

1. I don't understand what is the purpose of the line
Run
Click to expand...

As far as I can tell, it isn't going to do anything.

2. This line:
DoCmd.Close ("Students_Without_Matching_Notas")
Click to expand...

should fail, since it doesn't conform to the correct syntax for the
DoCmd.Close method. Have you got "On Error Resume Next" in force
somehow?

3. Does your append query, "addssnota", rely on the value of [ss] being
stored in a table already? If so, that won't have happened yet in the
AfterUpdate event of that control, because the current record won't have
been saved yet. You can force the record to be saved by executing the
line

RunCommand acCmdSaveRecord

before executing the append query -- presuming that all required fields
have been completed and the record is valid. Or you could run your code
in the *form's* AfterUpdate or AfterInsert event instead.
 
Thx for replying Dirk

My form is based on table students
To add a new student field ss(social security #) is required
the purpose is to add ss to table notas with the afterUpdate function

My codes are now:
Private Sub ss_AfterUpdate()
DoCmd.OpenQuery ("Students Without Matching Notas")
DoCmd.OpenQuery ("addssnota")
End Sub
-------------------------------------
Private Sub Form_AfterInsert()
RunCommand acCmdSaveRecord
End Sub

but still not update table notas :-(

herman
Dirk Goldgar said:
hermie said:
In the field [ss] on my data entry form I put code in the afterUpdate
field. The code runs but not update the field in table notas. First
I get the message: You are about to run an append query that will
modify data in your table. when I click OK a second message appears
which says: You are about to append 0 row(s) What should be 1 row

What is wrong with the code?

Private Sub ss_AfterUpdate()
DoCmd.OpenQuery ("Students_Without_Matching_Notas")
Run
DoCmd.Close ("Students_Without_Matching_Notas")
DoCmd.OpenQuery ("addssnota")
End Sub

any help is welcome

Herman
Click to expand...

1. I don't understand what is the purpose of the line
Run
Click to expand...

As far as I can tell, it isn't going to do anything.

2. This line:
DoCmd.Close ("Students_Without_Matching_Notas")
Click to expand...

should fail, since it doesn't conform to the correct syntax for the
DoCmd.Close method. Have you got "On Error Resume Next" in force
somehow?

3. Does your append query, "addssnota", rely on the value of [ss] being
stored in a table already? If so, that won't have happened yet in the
AfterUpdate event of that control, because the current record won't have
been saved yet. You can force the record to be saved by executing the
line

RunCommand acCmdSaveRecord

before executing the append query -- presuming that all required fields
have been completed and the record is valid. Or you could run your code
in the *form's* AfterUpdate or AfterInsert event instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
hermie said:
Thx for replying Dirk

My form is based on table students
To add a new student field ss(social security #) is required
the purpose is to add ss to table notas with the afterUpdate function

My codes are now:
Private Sub ss_AfterUpdate()
DoCmd.OpenQuery ("Students Without Matching Notas")
DoCmd.OpenQuery ("addssnota")
End Sub
-------------------------------------
Private Sub Form_AfterInsert()
RunCommand acCmdSaveRecord
End Sub

but still not update table notas :-(

herman
Dirk Goldgar said:
hermie said:
In the field [ss] on my data entry form I put code in the
afterUpdate field. The code runs but not update the field in table
notas. First
I get the message: You are about to run an append query that will
modify data in your table. when I click OK a second message appears
which says: You are about to append 0 row(s) What should be 1 row

What is wrong with the code?

Private Sub ss_AfterUpdate()
DoCmd.OpenQuery ("Students_Without_Matching_Notas")
Run
DoCmd.Close ("Students_Without_Matching_Notas")
DoCmd.OpenQuery ("addssnota")
End Sub

any help is welcome

Herman
Click to expand...

1. I don't understand what is the purpose of the line
Run
Click to expand...

As far as I can tell, it isn't going to do anything.

2. This line:
DoCmd.Close ("Students_Without_Matching_Notas")
Click to expand...

should fail, since it doesn't conform to the correct syntax for the
DoCmd.Close method. Have you got "On Error Resume Next" in force
somehow?

3. Does your append query, "addssnota", rely on the value of [ss]
being stored in a table already? If so, that won't have happened
yet in the AfterUpdate event of that control, because the current
record won't have been saved yet. You can force the record to be
saved by executing the line

RunCommand acCmdSaveRecord

before executing the append query -- presuming that all required
fields have been completed and the record is valid. Or you could
run your code in the *form's* AfterUpdate or AfterInsert event
instead.
Click to expand...
Click to expand...

No, that still isn't quite right. My idea was to *either* put the
RunCommand acCmdSaveRecord in the ss_AfterUpdate() event procedure, *or
else* move all the code from that procedure -- without the RunCommand
line -- to Form_AfterInsert(). Try this:

Private Sub ss_AfterUpdate()
RunCommand acCmdSaveRecord
DoCmd.OpenQuery ("Students Without Matching Notas")
DoCmd.OpenQuery ("addssnota")
End Sub

(and delete the Form_AfterInsert procedure.)

Be aware that I have no idea what the query "Students Without Matching
Notas" is, so I don't actually know if it's a meaningful part of this
process or not.
 
Hurray it works many thanks for your support, i learned again something
today like everyday

Students Without Matching Notas is the result of an find unmatched query
wizard

Herman

Be aware that I have no idea what the query "Students Without Matching
Notas" is, so I don't actually know if it's a meaningful part of this
process or not.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Dirk Goldgar said:
hermie said:
Thx for replying Dirk

My form is based on table students
To add a new student field ss(social security #) is required
the purpose is to add ss to table notas with the afterUpdate function

My codes are now:
Private Sub ss_AfterUpdate()
DoCmd.OpenQuery ("Students Without Matching Notas")
DoCmd.OpenQuery ("addssnota")
End Sub
-------------------------------------
Private Sub Form_AfterInsert()
RunCommand acCmdSaveRecord
End Sub

but still not update table notas :-(

herman
Dirk Goldgar said:
In the field [ss] on my data entry form I put code in the
afterUpdate field. The code runs but not update the field in table
notas. First
I get the message: You are about to run an append query that will
modify data in your table. when I click OK a second message appears
which says: You are about to append 0 row(s) What should be 1 row

What is wrong with the code?

Private Sub ss_AfterUpdate()
DoCmd.OpenQuery ("Students_Without_Matching_Notas")
Run
DoCmd.Close ("Students_Without_Matching_Notas")
DoCmd.OpenQuery ("addssnota")
End Sub

any help is welcome

Herman

1. I don't understand what is the purpose of the line

Run

As far as I can tell, it isn't going to do anything.

2. This line:

DoCmd.Close ("Students_Without_Matching_Notas")

should fail, since it doesn't conform to the correct syntax for the
DoCmd.Close method. Have you got "On Error Resume Next" in force
somehow?

3. Does your append query, "addssnota", rely on the value of [ss]
being stored in a table already? If so, that won't have happened
yet in the AfterUpdate event of that control, because the current
record won't have been saved yet. You can force the record to be
saved by executing the line

RunCommand acCmdSaveRecord

before executing the append query -- presuming that all required
fields have been completed and the record is valid. Or you could
run your code in the *form's* AfterUpdate or AfterInsert event
instead.
Click to expand...
Click to expand...

No, that still isn't quite right. My idea was to *either* put the
RunCommand acCmdSaveRecord in the ss_AfterUpdate() event procedure, *or
else* move all the code from that procedure -- without the RunCommand
line -- to Form_AfterInsert(). Try this:

Private Sub ss_AfterUpdate()
RunCommand acCmdSaveRecord
DoCmd.OpenQuery ("Students Without Matching Notas")
DoCmd.OpenQuery ("addssnota")
End Sub

(and delete the Form_AfterInsert procedure.)

Be aware that I have no idea what the query "Students Without Matching
Notas" is, so I don't actually know if it's a meaningful part of this
process or not.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top