Please help.

[sheela]

Hi:

I have been working on this problem from last 3 days and
desperately need some help.
I have posted my problem before, but didn't get much help.
I will try to restate clearly and hope I will get help
this time. I greatly appreciate any help.

The database is to store lab samples in freezers.
I am using Access 2003 on windows 2000.
We are using a form to enter multiple records into a
table. This works fine.
But the problem is every time the user has to open and
type all the values into the form.
Once he clicks on save, the values will be saved and the
form will be closed.
Many times he has to enter multiple entries and many field
values will be same as the previous entry.

I will paste the code here; I think the code explains
better.
The first entry works fine. The problem comes only when
the user clicks "yes" to the message box ie when he wants
enter next entry.
The problems I have are:

1) The first record (of the next entry) will be
automatically saved to the database, without checking
anything.
2) When the user retypes the new values on the form, the
values of bounded controls are going back to the
previously saved value.

The code follows:
_________________

Private Sub cmd1_click()
Dim k As Integer
Dim dbs As Database
Dim rstpositionexists As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM tblSample_Storage_Log WHERE " & _
"(((tblSample_Storage_Log.FreezerName)= """ & [Forms]!
[frmEnter_new_sampleMltPositions]![cmbFreezerName]
& """) " & _
"AND ((tblSample_Storage_Log.RackNumber)=" & [Forms]!
[frmEnter_new_sampleMltPositions]![cmbRackNumber] & ") " &
_
"AND ((tblSample_Storage_Log.Box)=" & [Forms]!
[frmEnter_new_sampleMltPositions]![cmbBox] & ") " & _
"and tblSample_Storage_Log.Position between " & [Forms]!
[frmEnter_new_sampleMltPositions]![txtStartPosition] & "
And " & [Forms]![frmEnter_new_sampleMltPositions]!
[txtEndPosition] & ")"

Set rstpositionexists = dbs.OpenRecordset(strSQL)

If rstpositionexists.RecordCount > 0 Then
MsgBox ("Sorry, there is already a sample stored
in some of these positions. The records can't be stored in
these locations")
DoCmd.Close
End If

With Me.RecordsetClone
For k = Me.txtStartPosition To Me.txtEndPosition
.AddNew

!Position = k
!Storedby = Me!txtStoredby
!StoredDate = Me!txtStoredDate
!StorageTemp = Me!txtStorageTemp
!FreezerName = Me!cmbFreezerName
.....
.....
.Update
Next k
End With
Me.RecordsetClone.Close

If MsgBox("Do you wish to enter more samples now?",
vbYesNo, "More Samples?") = vbYes Then
MsgBox ("Please enter the values for the new sample
and click on Save Next")

Me!txtStartPosition.SetFocus

Else:

DoCmd.Close
End If

End Sub
-----------------------------------------------------------
----------

On cmd2_click I have:

Private Sub cmd2_Click()

Cmd1_Click

End Sub





Thank you,
sheela

 

[Bandit]

Hi,
Does everyone including you get the same error? I was
thinking that it may be an issue with Jet 4.0. Does
everyone have the latest version for their system
installed? I ran into this a few weeks ago when an
application started to act 'weird' out of the blue but
when I updated the Jet 4.0 on all systems then all the
phantom problems vanished.

Hope this helps this issue but at least might stop some
potential issues. Here is the link to to download area
with each systems specific download:

http://support.microsoft.com/default.aspx?scid=kb;en-
us;239114

Have a great day,
Bandit

-----Original Message-----
Hi:

I have been working on this problem from last 3 days and
desperately need some help.
I have posted my problem before, but didn't get much help.
I will try to restate clearly and hope I will get help
this time. I greatly appreciate any help.

The database is to store lab samples in freezers.
I am using Access 2003 on windows 2000.
We are using a form to enter multiple records into a
table. This works fine.
But the problem is every time the user has to open and
type all the values into the form.
Once he clicks on save, the values will be saved and the
form will be closed.
Many times he has to enter multiple entries and many field
values will be same as the previous entry.

I will paste the code here; I think the code explains
better.
The first entry works fine. The problem comes only when
the user clicks "yes" to the message box ie when he wants
enter next entry.
The problems I have are:

1) The first record (of the next entry) will be
automatically saved to the database, without checking
anything.
2) When the user retypes the new values on the form, the
values of bounded controls are going back to the
previously saved value.

The code follows:
_________________

Private Sub cmd1_click()
Dim k As Integer
Dim dbs As Database
Dim rstpositionexists As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM tblSample_Storage_Log WHERE " & _
"(((tblSample_Storage_Log.FreezerName)= """ & [Forms]!
[frmEnter_new_sampleMltPositions]![cmbFreezerName]
& """) " & _
"AND ((tblSample_Storage_Log.RackNumber)=" & [Forms]!
[frmEnter_new_sampleMltPositions]![cmbRackNumber] & ") " &
_
"AND ((tblSample_Storage_Log.Box)=" & [Forms]!
[frmEnter_new_sampleMltPositions]![cmbBox] & ") " & _
"and tblSample_Storage_Log.Position between " & [Forms]!
[frmEnter_new_sampleMltPositions]![txtStartPosition] & "
And " & [Forms]![frmEnter_new_sampleMltPositions]!
[txtEndPosition] & ")"

Set rstpositionexists = dbs.OpenRecordset(strSQL)

If rstpositionexists.RecordCount > 0 Then
MsgBox ("Sorry, there is already a sample stored
in some of these positions. The records can't be stored in
these locations")
DoCmd.Close
End If

With Me.RecordsetClone
For k = Me.txtStartPosition To Me.txtEndPosition
.AddNew

!Position = k
!Storedby = Me!txtStoredby
!StoredDate = Me!txtStoredDate
!StorageTemp = Me!txtStorageTemp
!FreezerName = Me!cmbFreezerName
.....
.....
.Update
Next k
End With
Me.RecordsetClone.Close

If MsgBox("Do you wish to enter more samples now?",
vbYesNo, "More Samples?") = vbYes Then
MsgBox ("Please enter the values for the new sample
and click on Save Next")

Me!txtStartPosition.SetFocus

Else:

DoCmd.Close
End If

End Sub
---------------------------------------------------------- -
----------

On cmd2_click I have:

Private Sub cmd2_Click()

Cmd1_Click

End Sub





Thank you,
sheela

.

 

[Marshall Barton]

This seems like it's a little convoluted and I'm not at all
sure I followed all that code.

First off, let's get one thing straight, you should never,
ever use Close on RecordsetClone. You did not open that
recordset, Access did, and when you Close it, who knows what
Access is going to have to do to recover from that.

However, I don't think that's the core of your problems.
I'm pretty sure that at least part of the issue here is that
you're using a bound form to enter the data to be saved as
multiple records into the form's base table. At some point
the data you entered in the form's bound controls will be
saved to the table in addition to the records your code is
adding.

I think you might want to use an unbound form for this and
open the recordset on the table instead of using
RecordsetClone.

Think about this carefully to make sure the combination of
form and code are really doing what you think you're doing.
--
Marsh
MVP [MS Access]

I have been working on this problem from last 3 days and
desperately need some help.
I have posted my problem before, but didn't get much help.
I will try to restate clearly and hope I will get help
this time. I greatly appreciate any help.

The database is to store lab samples in freezers.
I am using Access 2003 on windows 2000.
We are using a form to enter multiple records into a
table. This works fine.
But the problem is every time the user has to open and
type all the values into the form.
Once he clicks on save, the values will be saved and the
form will be closed.
Many times he has to enter multiple entries and many field
values will be same as the previous entry.

I will paste the code here; I think the code explains
better.
The first entry works fine. The problem comes only when
the user clicks "yes" to the message box ie when he wants
enter next entry.
The problems I have are:

1) The first record (of the next entry) will be
automatically saved to the database, without checking
anything.
2) When the user retypes the new values on the form, the
values of bounded controls are going back to the
previously saved value.

The code follows:
_________________

Private Sub cmd1_click()
Dim k As Integer
Dim dbs As Database
Dim rstpositionexists As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM tblSample_Storage_Log WHERE " & _
"(((tblSample_Storage_Log.FreezerName)= """ & [Forms]!
[frmEnter_new_sampleMltPositions]![cmbFreezerName]
& """) " & _
"AND ((tblSample_Storage_Log.RackNumber)=" & [Forms]!
[frmEnter_new_sampleMltPositions]![cmbRackNumber] & ") " &
_
"AND ((tblSample_Storage_Log.Box)=" & [Forms]!
[frmEnter_new_sampleMltPositions]![cmbBox] & ") " & _
"and tblSample_Storage_Log.Position between " & [Forms]!
[frmEnter_new_sampleMltPositions]![txtStartPosition] & "
And " & [Forms]![frmEnter_new_sampleMltPositions]!
[txtEndPosition] & ")"

Set rstpositionexists = dbs.OpenRecordset(strSQL)

If rstpositionexists.RecordCount > 0 Then
MsgBox ("Sorry, there is already a sample stored
in some of these positions. The records can't be stored in
these locations")
DoCmd.Close
End If

With Me.RecordsetClone
For k = Me.txtStartPosition To Me.txtEndPosition
.AddNew

!Position = k
!Storedby = Me!txtStoredby
!StoredDate = Me!txtStoredDate
!StorageTemp = Me!txtStorageTemp
!FreezerName = Me!cmbFreezerName
....
....
.Update
Next k
End With
Me.RecordsetClone.Close

If MsgBox("Do you wish to enter more samples now?",
vbYesNo, "More Samples?") = vbYes Then
MsgBox ("Please enter the values for the new sample
and click on Save Next")

Me!txtStartPosition.SetFocus

Else:

DoCmd.Close
End If

End Sub
-----------------------------------------------------------
----------

On cmd2_click I have:

Private Sub cmd2_Click()

Cmd1_Click

End Sub





Thank you,
sheela