PLEASE HELP

  • Thread starter Thread starter sheela
  • Start date Start date
S

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
 
Sheela,
Remove the colon after the word "Else" in your code. When you put a colon
after a word it makes that word a label and, thus, your DoCmd.Close
statement is actually part of the IF statement and not part of an Else
statement and is firing every time the button is clicked, regardless of
results.

Lynn Trapp
MS Access MVP

sheela said:
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
Click to expand...
 
Lynn Trapp:
Thank you for your reply.
Even with the colon it worked as part of ELSE statement,
not as aprt of IF.
The problem is, with the
Me!txtStartPosition.SetFocus
1)The current record is automatically saved to the
database.
2)Then it is being changed to the previous value.

Is there way to prevent automatically saving the form's
current record to the DB.
And how do I retain the new value in the text box ( it is
bounded to a table field), with out changing back to the
previuosly saved value?
TIA,
sheela
-----Original Message-----
Sheela,
Remove the colon after the word "Else" in your code. When you put a colon
after a word it makes that word a label and, thus, your DoCmd.Close
statement is actually part of the IF statement and not part of an Else
statement and is firing every time the button is clicked, regardless of
results.

Lynn Trapp
MS Access MVP

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
Click to expand...


.
Click to expand...
 
Back
Top