Insert a record and another record does not work

[dst393 via AccessMonster.com]

I have form that a user enters data. The users clicks the command button and
inserts the record in a table in the database. After the insert is complete,
the form is clear to enter more data. When the user enters more data, he
will click on the command button again. He does not receive any errors.
When I check the table to verify the records are there, only the first record
is there. The second one is not. All I am using is the insert into sql
statement and then the docmd.runsql to execute it. Am I missing something?

 

[Guest]

Is this a bound form or an unbound form?
It will not be possible to tell unless we know that. It would be helpful if
you would post the code in the command button's Click event so we can see
what it is trying to do.

 

[dst393 via AccessMonster.com]

I am post part of the code because it is so long....

strInsert = "Insert into Maintable (CourseTitle, Assignee, Location,
StartDate, EndDate, " & _
"LocationRvedDate, DateInstructorCleared, PostMTB,
PostTCRS, PostSATERNDte, SWRDte, RoomCapacity, " & _
"CoordProvDte, CloseOutDte, InvoiceRecved, EvalTechSent,
PostDateTC, Vendor, VendorAddress, " & _
"VidRema, MaxOfStudents, EstimatedCost, EquipmentNeeded,
EntranceG, POC, POCPhone, POCFax," & _
"PostCCLog, EvalRecved, MatRetToNSTC, CopyToSPLC,
FinanSent, TVViewDte, " & _
"Cancelled, Postponed, Starttime, Endtime, Remarks,
Category, Targetaudience, " & _
"Rescheduled, CoursNumber, Coursetype, Instructor,
ScheduleOffID, SiteMap, SelfReg) VALUES ("

If IsNull(txtCT.Value) Then 'Course Title
MsgBox "Please enter the course title"
strInsert = ""
Exit Sub
Else
strCrsNme = txtCT.Value
strInsert = strInsert & "'" & strCrsNme & "', "
End If
....
If IsNull(ckbxSR.Value) Then 'Self Registration
ckSelReg = False
strInsert = strInsert & "False)"
Else
ckSelReg = ckbxSR.Value
strInsert = strInsert & "True)"
End If
DoCmd.RunSQL strInsert

Then it clears all the data out of the fields on the form. When data is
entered a second time, the record is not in the table. I would have to close
the form and reopen it and enter data each time. It sounds as though I have
to refresh the form.

Is this a bound form or an unbound form?
It will not be possible to tell unless we know that. It would be helpful if
you would post the code in the command button's Click event so we can see
what it is trying to do.

I have form that a user enters data. The users clicks the command button and
inserts the record in a table in the database. After the insert is complete,

[quoted text clipped - 3 lines]

is there. The second one is not. All I am using is the insert into sql
statement and then the docmd.runsql to execute it. Am I missing something?

 

[Guest]

You might try doing a Requery on the form after the Insert, then open the
table while the form is still open to see if the second new record is there.
I don't see anything in the code you posted that jumps out.
I am not sure a Requery works on an unbound form, because I don't use
unbound forms for data entry.

Is there any particular reason you have chosen to use an unbound form?
Bound forms are a lot easier to work with and take a lot less coding.

I am post part of the code because it is so long....

strInsert = "Insert into Maintable (CourseTitle, Assignee, Location,
StartDate, EndDate, " & _
"LocationRvedDate, DateInstructorCleared, PostMTB,
PostTCRS, PostSATERNDte, SWRDte, RoomCapacity, " & _
"CoordProvDte, CloseOutDte, InvoiceRecved, EvalTechSent,
PostDateTC, Vendor, VendorAddress, " & _
"VidRema, MaxOfStudents, EstimatedCost, EquipmentNeeded,
EntranceG, POC, POCPhone, POCFax," & _
"PostCCLog, EvalRecved, MatRetToNSTC, CopyToSPLC,
FinanSent, TVViewDte, " & _
"Cancelled, Postponed, Starttime, Endtime, Remarks,
Category, Targetaudience, " & _
"Rescheduled, CoursNumber, Coursetype, Instructor,
ScheduleOffID, SiteMap, SelfReg) VALUES ("

If IsNull(txtCT.Value) Then 'Course Title
MsgBox "Please enter the course title"
strInsert = ""
Exit Sub
Else
strCrsNme = txtCT.Value
strInsert = strInsert & "'" & strCrsNme & "', "
End If
....
If IsNull(ckbxSR.Value) Then 'Self Registration
ckSelReg = False
strInsert = strInsert & "False)"
Else
ckSelReg = ckbxSR.Value
strInsert = strInsert & "True)"
End If
DoCmd.RunSQL strInsert

Then it clears all the data out of the fields on the form. When data is
entered a second time, the record is not in the table. I would have to close
the form and reopen it and enter data each time. It sounds as though I have
to refresh the form.

Is this a bound form or an unbound form?
It will not be possible to tell unless we know that. It would be helpful if
you would post the code in the command button's Click event so we can see
what it is trying to do.

I have form that a user enters data. The users clicks the command button and
inserts the record in a table in the database. After the insert is complete,

[quoted text clipped - 3 lines]

is there. The second one is not. All I am using is the insert into sql
statement and then the docmd.runsql to execute it. Am I missing something?

 

[dst393 via AccessMonster.com]

I am retrieving data from another table and putting the data in another. I
don't want to bind my controls to one table when I am generating data from
another. For example, the course number field. When the form opens, it
generates the new course number for the next course by getting the
information from another table. When the user completes the data for that
particular course, it puts the data in another table and add the new course
and course number if it is new to the other table.

You might try doing a Requery on the form after the Insert, then open the
table while the form is still open to see if the second new record is there.
I don't see anything in the code you posted that jumps out.
I am not sure a Requery works on an unbound form, because I don't use
unbound forms for data entry.

Is there any particular reason you have chosen to use an unbound form?
Bound forms are a lot easier to work with and take a lot less coding.

I am post part of the code because it is so long....

[quoted text clipped - 46 lines]

 

[Guest]

Have you tried the Requery yet?
If that doesn' work, I would suggest rather than the SQL (which is what I
would prefer, if it works) would be to use a DAO recordset. That does have a
Requery method and will work.

The problem with the unbound form is I don't know if the plain old
Me.Requery will work because the form doesn't have a recordset if it is
unbound, so what will it requery?

Does the first record go in without closing the form?
How are you determining the first goes in but the second does not?
This is all very curious to me.

If that doesn't work

I am retrieving data from another table and putting the data in another. I
don't want to bind my controls to one table when I am generating data from
another. For example, the course number field. When the form opens, it
generates the new course number for the next course by getting the
information from another table. When the user completes the data for that
particular course, it puts the data in another table and add the new course
and course number if it is new to the other table.

You might try doing a Requery on the form after the Insert, then open the
table while the form is still open to see if the second new record is there.
I don't see anything in the code you posted that jumps out.
I am not sure a Requery works on an unbound form, because I don't use
unbound forms for data entry.

Is there any particular reason you have chosen to use an unbound form?
Bound forms are a lot easier to work with and take a lot less coding.

I am post part of the code because it is so long....

[quoted text clipped - 46 lines]

is there. The second one is not. All I am using is the insert into sql
statement and then the docmd.runsql to execute it. Am I missing something?

 

[dst393 via AccessMonster.com]

Yes the first record goes in without closing the form. Then the fields are
cleared and I would enter more data. When I click the command button to
store the second record, it appears that it worked, but when I go into the
table the record is not there.

Have you tried the Requery yet?
If that doesn' work, I would suggest rather than the SQL (which is what I
would prefer, if it works) would be to use a DAO recordset. That does have a
Requery method and will work.

The problem with the unbound form is I don't know if the plain old
Me.Requery will work because the form doesn't have a recordset if it is
unbound, so what will it requery?

Does the first record go in without closing the form?
How are you determining the first goes in but the second does not?
This is all very curious to me.

If that doesn't work

I am retrieving data from another table and putting the data in another. I
don't want to bind my controls to one table when I am generating data from

[quoted text clipped - 18 lines]

 

[dst393 via AccessMonster.com]

I have it working...

Yes the first record goes in without closing the form. Then the fields are
cleared and I would enter more data. When I click the command button to
store the second record, it appears that it worked, but when I go into the
table the record is not there.

Have you tried the Requery yet?
If that doesn' work, I would suggest rather than the SQL (which is what I

[quoted text clipped - 16 lines]