bound DAO.RecordSet on form load 1

[Klepdog]

I have been running into some issues trying to find specific records and then saving changes. Each time I try to save the edited record the code creates a new record. Here is the coding that I am using for the form. Any suggestions on what I am doing wrong? Each field in the recordset is bound to a field in the form.

Option Compare Database

Private Sub form_load()

Dim db As DAO.Database
Dim rsViewUpdate As DAO.Recordset

Set db = CurrentDb
Set rsViewUpdate = db.OpenRecordset("select PINVSTN.* from PINVSTN", dbOpenDynaset)
Set Me.Form.Recordset = rsViewUpdate

Set rsViewUpdate = Nothing
Set db = Nothing

End Sub

Private Sub View_Update_Part_Click()

Dim db As DAO.Database
Dim rsViewUpdate As DAO.Recordset
Dim varNSN As Variant
Dim varPN As Variant
Dim strNSN As String
Dim strPN As String
Dim responce As String
Dim responce1 As String
Dim responce2 As String
Dim responce3 As String
Dim responce4 As String

Set db = CurrentDb

varNSN = Me.StockNumb1
varPN = Me.Part_num1

If IsNull(varNSN) Then
If IsNull(varPN) Then
responce = MsgBox("Please enter either the Stock Number or Part Number.", vbOKOnly)
If responce = vbOK Then
Exit Sub
End If
End If
End If

Set rsViewUpdate = db.OpenRecordset("select PINVSTN.* from PINVSTN", dbOpenDynaset)

If Not IsNull(varNSN) Then
With rsViewUpdate
.MoveFirst
Do Until .EOF
strNSN = !STOCK_NUM1
If Not varNSN = strNSN Then
If .EOF Then
responce1 = MsgBox("" & varNSN & " not found. Please re-enter the Stock Number.", vbOKOnly)
If responce1 = vbOK Then
Exit Do
End If
End If
.MoveNext
Else
Me.StockNumb1 = !STOCK_NUMB1
Me.Part_num1 = !Part_num1
Me.Part_Nomen = !Nomen
Me.Location1 = !Location
Me.QtyUP1 = !QUAN_UPACK
Me.Base_SupLoc = !BASE_SULOC
Me.BaseSupQty = !QUAN_BSULO
Me.qty1 = !QUAN_STOCK
Me.Issue_Unit1 = !ISSUE_UNIT
Me.Qty_Reord1 = !QUAN_REORD
Me.Cost1 = !COST
Me.DateLastIss1 = !LAST_IS_DT
Me.DateReord = !REORDERDATE
Me.REORDERFLAG = !REORDERFLAG
responce4 = MsgBox("Is this the Stock Number you require?", vbYesNo)
If responce4 = vbNo Then
.MoveNext
Else
Exit Do
End If
If .EOF Then
responce1 = MsgBox("" & varNSN & " not found. Please re-enter the Stock Number.", vbOKOnly)
If responce1 = vbOK Then
Me.StockNumb1 = Null
Me.Part_num1 = Null
Me.Part_Nomen = Null
Me.Location1 = Null
Me.QtyUP1 = Null
Me.Base_SupLoc = Null
Me.BaseSupQty = Null
Me.qty1 = Null
Me.Issue_Unit1 = Null
Me.Qty_Reord1 = Null
Me.Cost1 = Null
Me.DateLastIss1 = Null
Me.DateReord = Null
Me.REORDERFLAG = Null
Exit Do
End If
End If
Loop
End With

If Not IsNull(varPN) Then
With rsViewUpdate
.MoveFirst
Do Until .EOF
Do While IsNull(rsViewUpdate!Part_num1)
rsViewUpdate.MoveNext
If Not IsNull(rsViewUpdate!Partnum1) Then
Exit Do
End If
Loop
strPN = !Part_num1
If Not varPN = strPN Then
.MoveNext
If .EOF Then
responce3 = MsgBox("" & varPN & " not found. Please re-enter Part Number.", vbOKOnly)
If responce3 = vbOK Then
Me.StockNumb1 = Null
Me.Part_num1 = Null
Me.Part_Nomen = Null
Me.Location1 = Null
Me.QtyUP1 = Null
Me.Base_SupLoc = Null
Me.BaseSupQty = Null
Me.qty1 = Null
Me.Issue_Unit1 = Null
Me.Qty_Reord1 = Null
Me.Cost1 = Null
Me.DateLastIss1 = Null
Me.DateReord = Null
Me.REORDERFLAG = Null
Exit Do
End If
End If
Else
Me.StockNumb1 = !STOCK_NUMB1
Me.Part_num1 = !Part_num1
Me.Part_Nomen = !Nomen
Me.Location1 = !Location
Me.QtyUP1 = !QUAN_UPACK
Me.Base_SupLoc = !BASE_SULOC
Me.BaseSupQty = !QUAN_BSULO
Me.qty1 = !QUAN_STOCK
Me.Issue_Unit1 = !ISSUE_UNIT
Me.Qty_Reord1 = !QUAN_REORD
Me.Cost1 = !COST
Me.DateLastIss1 = !LAST_IS_DT
Me.DateReord = !REORDERDATE
Me.REORDERFLAG = !REORDERFLAG
responce2 = MsgBox("Is this the Part Number you require?", vbYesNo)
If responce2 = vbNo Then
.MoveNext
Else
Exit Do
End If
Loop
End With
End If
End If

End Sub

Private Sub Save_Edit()

Dim db As DAO.Database
Dim rsViewUpdate As DAO.Recordset
Dim responce4 As String
Dim responce5 As String
Dim varNSN1 As Variant
Dim varPN1 As Variant

Set db = CurrentDb
Set rsViewUpdate = db.OpenRecordset("select PINVSTN.* from PINVSTN", dbOpenDynaset)

If IsNull(varNSN) Then
If IsNull(varPN) Then
responce4 = MsgBox("Please enter either the Stock Number or Part Number.", vbOKOnly)
If responce4 = vbOK Then
Exit Sub
End If
End If
End If

With rsViewUpdate
.Edit
!STOCK_NUMB1 = Me.StockNumb1
!Part_num1 = Me.Part_num1
!Nomen = Me.Part_Nomen
!Location = Me.Location1
!QUAN_UPACK = Me.QtyUP1
!BASE_SULOC = Me.Base_SupLoc
!QUAN_BSULO = Me.BaseSupQty
!QUAN_STOCK = Me.qty1
!ISSUE_UNIT = Me.Issue_Unit1
!QUAN_REORD = Me.Qty_Reord1
!COST = Me.Cost1
!LAST_IS_DT = Me.DateLastIss1
!REORDERDATE = Me.DateReord
!REORDERFLAG = Me.REORDERFLAG
.Update
End With
varNSN1 = Me.StockNumb1
varPN1 = Me.Part_num1
responce5 = MsgBox("" & varNSN1 & "" & varPN1 & " successfully changed.", vbOKOnly)
If responce5 = vbOK Then
Exit Sub
End If

End Sub

 

[Klepdog]

This is what I wish. The record that I want to protect is an empty record. I am using this record on form open to keep the form fields empty until the part is searched. Basically a place holder. Could there be a better way? Is there a way to not allow the user input another empty record?

 

[PHV]

That says you can delete only if there are values in both fields
Sorry to disagree.
That says you can delete only if there are value in at least one of the fields

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Klepdog]

ok how should it be written if both fields in the protected record has a null value? I need to protect from edits and deletion.

 

[MajP]

You were correct, as PHV pointed out. I missed the outer not.

= not (isnull(me.searchstock) and isnull(me.searchpart))
so if both are null
= not (true and true)
= not (true)
= false

 

[Klepdog]

ok. would it be easier to add a field to the table, say call it protect, make it an integer, give it a value of maybe 1. Add a hidden field in each form and use it for either allowedit or allowdeletion control for that record

= not 1

 

[MajP]

A little easier. You do not have to add the field to the form, you can still reference the field even if not on the form as long as it is in the recordsource. But, again why do you need this special record? There is probably a cleaner way to do this. My guess this is the default settings for a new record. You can do an insert query to add a new record or you can set default properties.

 

[Klepdog]

what I did was do a clear fields command button with the following code

dim varnsn as variant
dim varpn as variant
strsql = "select PINVSTN.* from PINVSTN where [STOCK_NUM1] = isnull(varnsn) and ]PART_NUM1] isnull(varpn)"
me.recordsource = strsql

I also put this in the form on load property. it works for what I was trying to accomplish, which was to empty fields when the form comes up and to clear the fields before the next search or edit depending on the form. This is why I had a special record. Was there a better way to do this?

 

[MajP]

Yeah. Normally to clear the fields of a bound form.
Either change the filter to return no records
me.filter = "[Stock_Num1] = 'some value it would never be'"
me.filteron = true

or change the recordsource to return no records
me.recordsource = "Select * from PINVSTN where true = false

 

[Klepdog]

ok, I will try this. The database I am working on is at work. I will not be able to do anything until Tuesday. Unfortuantly, the internet system at work will not allow me to post on this site. i will have to wait until I get home that night to let you know of the results.

 

[Klepdog]

I added the allowedits, allowdeletions, and allowadds like we discussed. There was some minor adjustments to the coding that I had make. Added a new field to the table and used it for the allowedits, allowdeletions. The changes did what I was hoping for. I sure appreciate the help on this database. Thanks again.

 

[Klepdog]

MajP, I have another question. I am working on another database and I was wondering if there is a simpler way of checking to see if fields are empty. The form I am talking about contains 5 fields that must contain a value entered by the user. Can I check each field all at once or must I check ea individually with an If IsNull() Then statement. If I can what type statement would I need.