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

 

[MajP]

I just have to ask why unbound? Not to be critical, but there is just a lot wrong here. In fact you are not even doing a correct unbound form, but you are both binding the form to a recordset, and setting the individual fields as well. You could make this work, but there are some big problems. Is this an academic exercise?

Fyi a dao recordset has a findfirst method. You do not have to loop looking for a record
Most people would not create a recordset without a where clause, and then loop it looking for something.
You would create a recordset with a where clause in your sql statement
You are getting new records because you are creating a new recordset and you are both bound and updating by dao.

If you had to do this I would create the RS as a class level variable

Public RsViewUpdate as DAO.Recordset
then do not bind the form
Then build the recordset with a where clause in your sql to return a single vpn
Now all modules can work with the Recordset

 

[Klepdog]

First of all. I have had no training or guidance on how to program with any database or program. What I have learned has been through trial and error plus a programmers reference guide. The fact that I have successfully made a few Access programs perform at work has led my boss to dump this on me. I am not a programmer. Just trying to get something to work.

 

[MajP]

Again not trying to be critical, but why go unbound? Unbound applications are far more complicated than bound, and usually requires a good reason in my opinion. You are reinventing the wheel, and requiring a lot more work. The fact that you are not a seasoned developer, makes even less sense to go unbound. Make this a bound form, and come back if you need specific features/functions you want to add. If you made this a bound form it would be the standard way of doing business, and most things would be trivial.

 

[Klepdog]

I originally had the form unbound, but was having problems of not being able to save the edited record so I tried binding the form. As you can tell it did not work. I have yet to learn how to create a public function and have it pass a set of records back to the form. Everytime that I have tried, I got an error invalid use of function or something. So I have normally tried to keep everthing on the form.

All I am trying to do is access a set of records using a stock number variable or part number as the search criteria. Return those records for display (displaying one record at a time) asking if that particular record is the correct record. (There are records in the database that have a stock number with 2 or 3 different part numbers(same part) and instances of records having the same part number (different parts)but different stock number.) If not, then go to the next record and check. Finally allow the user to edit the record and save the change.

If I set rsviewupdate as a public recordset in a class module and search for the records I want, how do I call those records back to the form, verify if it is the correct record, edit the record and save the change using separate command clicks? Basically allow each private sub command click to call the public recordset find the right record pass the recordset, set to the correct record, to next private sub command click, i.e. edit the record, and such. All I really need is the basic idea of how to call recordset back to the form.

 

[MajP]

Again you could make this work, but this is a Rude Golberg approach and it is not worth it in my opinion. You are making a large amount of work for yourself. There is just not reason to do this with recordsets when you can do this with all the native access controls.

So if it was me
1) I would probably have a form with two combos. One that returns all possible Partnumbers, one that returns all possible Stocknumbers. (assuming we are not talking 10s of thousands of records)
2) once the user picks one of these (no need to verify if a record exists because they are pulling from the combo) it would query a continous bound form for either the part or stock number.
3) the user can scroll through the continous form and click on one to edit if it is correct.
4) This pops open a bound form for that record.

This requires very little code.

Sure we can help you fix your code, but IMO that is like helping you to build a robot to clean your dirty floors. You have vacums and brooms you can use and we can help you with using them. If you need a robot because it is some very specific floor, then go with the overhead of building a robot, maintaining a robot, and using it.
If willing to go the conventional route, we can definitely help.

 

[MajP]

Here is a simple demo

http://www.4shared.com/file/Jp2J460Rce/DemoPart.html

Just like your search the user can search by order ID or product ID or both.
Once they do this it returns a list matching the criteria.
The user can click on an item and then edit that specific record.

The extent of the entire code

Private Sub cmboProduct_afterUpdate()
  FilterForm
  
End Sub
Private Sub FilterForm()
  Dim strSql As String
  strSql = "SELECT [Order Details].OrderID, [Order Details].ProductID, Products.ProductName FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID"
  If Not (IsNull(cmboOrder) Or IsNull(cmboProduct)) Then
    strSql = strSql & " where [Order Details].ProductID = " & cmboProduct & " OR OrderID = " & cmboOrder
    Me.RecordSource = strSql
  ElseIf Not IsNull(cmboOrder) Then
    strSql = strSql & " WHERE OrderID = " & cmboOrder
    Me.RecordSource = strSql
  ElseIf Not IsNull(cmboProduct) Then
    strSql = strSql & " WHERE [Order Details].ProductID = " & cmboProduct
    Me.RecordSource = strSql
  Else
    Me.RecordSource = ""
  End If
End Sub

Private Sub cmdEdit_Click()
  Dim strWhere As String
  strWhere = "ProductID = " & Me.ProductID & " AND OrderID = " & Me.OrderID
  DoCmd.OpenForm "frmDetails", acNormal, , strWhere, acFormEdit, acDialog
  Me.Requery
End Sub

Private Sub Form_Load()
  Me.RecordSource = "SELECT [Order Details].OrderID, [Order Details].ProductID, Products.ProductName FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID where True = false"
End Sub

 

[Klepdog]

I have been giving it some thought. I think I may be starting to understand your earlier post. So, if I declare the recordset public in a class module along with a couple of variables that need to stay constant through out the life of the form I should do the following:

Option Compare Database

Option Explicit

Public varNSN As Variant
Public varPN As Variant
Public strNSN As String
Public strPN As String
Public rsViewUpDate As DAO.Recordset
Set rsViewUpDate = db.OpenRecordset("select PINVSTN.* from PINVSTN", dbOpenDynaset)

varNSN = Form_FrmViewUpdatePart.Stock_Numb1
varPN = Form_FrmViewUpdatePart.Part_Num1

Then I could manipulate the recordset from the form using a .filter with the filter being my variables varNSN or varPN. If this is true should the "set reViewpdatePart" be part of the class module or the form command click event coding. Would the current record stay current when I use the .edit / .update as part of a separate command click?

 

[MajP]

Sorry, hopefully someone else is willing to help. As I said, in my opinion your approach is overly complicated and and makes no sense to me. I can provide suggestions on different approaches to do this correctly. IMO you need simply one or more bound forms, with a couple filters or modified queries based on the part of stock number. The amount of code should be very small
I just steer away from helping people fix their broken approaches. The reason is it becomes a continuous band-aid on top of another band-aid. Not saying you cannot make this work, because with enough effort you can. Other people may be willing to help, but I prefer to do things cleanly and not build Rude Goldberg machines. You seem overly committed to your approach, and not open to suggestions. So hopefully another one of the MVPs sees merit in this approach and is willing to help. Good luck.

 

[Klepdog]

IMO it is lazy not to do the task right. I am sorry that you think that the way I am trying to do this is wrong, but to me it seems the correct way. But I am smart enough to realize that I do not enough knowledge on programming and need to ask for help. How could it be a RUDE GOLDBERG program if coding is as small (not patched work) as possible and does the job correctly. I will try your approach, but it makes me wonder why use more than one form when it should be no problem to have one form do everything that is needed. I would have to have two combo boxes that each have to list over a thousand records. This would time consuming for the user to look at each record and to me be inefficient. This is why I would like the user enter either the part number or stock number and have the program find the record, then allow the user to edit and save the change to the record. I could have just made a query, bind it to a subform form and have the user sort through each record.

 

[MajP]

I would have to have two combo boxes that each have to list over a thousand records.

Access comboboxes have autocomplete as the default feature. Which means if the user want to search for "abc4555", and they type "a" it scrolls to the first "a" word. If they then type "b" it will scroll to the first "ab" record etc. So it is no more difficult then typing in a standard text box, except you have immediate confirmation that a record exists. I am not a big fan of typing into a box then hitting a button, only to be told that the record does not exist for that choice. I rather be choosing from the list of possible choices. If the possible choices are really long I prefer to build a find as you type list. So if I type "a" in the combo it filters the combo to those choices that start with an "a" and so on.
However, assuming the part numbers are more than a couple of characters. I can almost guarantee you even with a couple thousand records, I can scroll down a sorted list and find a part number to search as fast or faster than someone can type it in and then click a button and search for it. Especially if the part numbers are long. But the combos are just an idea, and you can base your query and filter just as easily on two text boxes.

This is why I would like the user enter either the part number or stock number and have the program find the record, then allow the user to edit and save the change to the record.

So that is exactly what the demo does. Instead of part or stock, the demo uses Product or Order ID. It used combos, but those could have been textboxes or listboxes. You pick a product or order ID and it returns the records that match the search.

Sure this can be done in a single form, but you said you want to display to the user the matching records one by one than ask them if it is the correct record. The demo does this in a continuous form so that the user can pick from that group a specific record.

Return those records for display (displaying one record at a time) asking if that particular record is the correct record. (There are records in the database that have a stock number with 2 or 3 different part numbers(same part) and instances of records having the same part number (different parts)but different stock number.) If not, then go to the next record and check.

So not sure how you wanted that effect. I demoed it with a continuous form of those that match one or both criteria. The user can see all the possible matches and then click on one to edit. This assumes that for display purposes you are limited to room for showing all the information, you need to pop up in a detail view to get all the fields. If real estate is not an issue than you could edit right on the main form. You sound like you want some kind of wizard where you click forward and next buttons and get prompted "is this your record".
If you want to do that on the same form, then just scroll through the found list of records in single form view.

But bottom line is people do this all the time. Build a form with some unbound controls (listboxes, textboxes, comboboxes) that allows them to filter the records. Either by applying a form filter or changing the forms recordsource (query). Then edit the found records. No need for unbound forms, no need for recordsets, and little if any code.

 

[MajP]

Updated demo.

http://www.4shared.com/file/S43m_oQTba/MajPDemoSearchPart.html

Maybe you will see with this. Added a second form to demo a different approach to filter, and return the found records in single form view. You then can scroll through the records to find the one you want and edit it.
There are close to a thousand OrderIDs in the first combo box. You can scroll very quickly (within seconds) through the sorted list or instead type it in and watch it autoexpand to the item you are looking for. Then also try typing in and order ID that does not exist, and you get immediate feedback that there is no order with that id. The filtering and feedback are immediate, no hitting a button only to be told the number does not exist. Also allows you to quickly look for a number if you only no part of the ID or name.

The entire code

Private Sub cmboOrder_afterUpdate()
  FilterForm
End Sub
Private Sub cmboOrder_enter()
  Me.cmboOrder.SetFocus
  Me.cmboOrder.Dropdown
End Sub

Private Sub cmboProduct_afterUpdate()
  FilterForm
End Sub
Private Sub FilterForm()
  Dim strFilter As String
  If Not (IsNull(cmboOrder) Or IsNull(cmboProduct)) Then
    strFilter = "[Order Details].ProductID = " & cmboProduct & " OR [Order Details].OrderID = " & cmboOrder
  ElseIf Not IsNull(cmboOrder) Then
    strFilter = "[Order Details].OrderID = " & cmboOrder
  ElseIf Not IsNull(cmboProduct) Then
   strFilter = "[Order Details].ProductID = " & cmboProduct
  Else
    strFilter = ""
  End If
  Me.Filter = strFilter
  Me.FilterOn = True
  Me.Recordset.MoveLast
  Me.Recordset.MoveFirst
  MsgBox "Your filter returned " & Me.Recordset.RecordCount & " records. Scroll through to find records to edit."
End Sub
Private Sub cmboProduct_enter()
  Me.cmboProduct.SetFocus
  cmboProduct.Dropdown
End Sub

Private Sub cmdClear_Click()
  Me.cmboOrder = Null
  Me.cmboProduct = Null
  Me.FilterOn = False
End Sub

Private Sub Form_Load()
  Me.Filter = "[Order Details].OrderID = 0"
  Me.FilterOn = True
End Sub

 

[Klepdog]

Sorry for not getting back sooner, but I was away yesterday and at work today. Ok, I used the coding you provided earlier and modified it to my form. It took a while to modify as I kept getting syntax errors for missing operators on the select statement. Here is what it looks like now.

Private Sub View_Update_Part()
On Error Goto Err_View_Update_Part

Filter Form

Exit_View_Update_Part_Click:
[indent]Exit Sub
Err_View_Update_Part_Click:
MsgBox Err.Description[/indent]
[indent]Resume Exit_View_Update_Part_Click:[/indent]

End Sub

Private Sub FilterForm()
Dim strsql as String
Dim varNSN as Variant
Dim varPN as Variant
Dim Response as String

varNSN = Me.SearchStock
varPN = Me.SearchPart

strsql = "select PINVSTN.* from PINVSTN"
If (IsNull(varNSN) And IsNull(varPN)) Then
response = MsgBox("Please enter either the Stock Number or Part Number.", vbOKOnly)
[indent]If response = vbOK Then
[indent]Exit Sub[/indent]
[indent]End If[/indent]
ElseIf Not IsNull(varNSN) Then
[indent]strsql = & " where [STOCK_NUM1] like """ & varNSN & """"
Me.RecordSource = strsql[/indent]
ElseIf Not IsNull(varPN) Then
[indent]strsql = " where [PART_NUM1] like """ & varPN & """"
[indent]Me.RecordSource = strsql[/indent]
Else
[indent]Me.RecordSource = ""
End If[/indent][/indent]
End Sub

For some reason if I did not add the extra quotation marks during the where clause, every time I searched for a Stock Number or Part Number with Alphanumeric characters I got the syntax error I spoke about above. Is there a reason for this? Anyways, I got the form to work just like you said. Also used the Me.RecordSource plus the FindFilter on other forms. I struggled on a form called Add_New_Part trying to save a new record.

I do have a couple of more questions. First on my form View_Update_Part I have record navigation turned on, but I was wondering is it possible to prevent a new record from occurring on this form. I do not want the user to be able to do this on this form. The second is there a way to protect one record from being deleted or altered at anytime. I use this record as an empty record in a bit of coding. And last with the code working do I still need error trapping or should I have it on Private Sub that has any code?

Thanks Don

 

[MajP]

When writing sql text literals are surrounded by quotes. Normally single quotes. Numerics do not. Dates get pounds sign.

.... Where CompanyName = 'Amaxon'
.... Where TeamID = 6
.... Where StartDate = #1/1/2014#

There is a property on the form for allowAdditions, AllowDeletions, AllowEdits

To keep a specific record from being deleted on a form use the forms beforedeleteconfirm

http://msdn.microsoft.com/en-us/library/office/bb256700(v=office.12).aspx

 

[Klepdog]

Thanks on the allowAdditions. The record that I am worried about is used on two forms. One form allows deletions and already asks the question do you want to delete record. The other form is designed to allow edits to records. This is why I was hoping for a way to protect the one record in a properties or coding to prevent accidental change or delete. What about the question on error trapping.

 

[Klepdog]

I looked at the link you gave and I am confused. How could I as the programmer using this code to prevent the record from deleted. I saw the line with message delete canceled by programmer, but I could not see how the record could be identified. What am I missing

 

[MajP]

In the del confirm check if the record they are trying to delete is your record.
If me.id = 1234 then ....
Cancel = true

So however you uniquely define a record, check for it. If found tell the user they cannot delete. Then cancel the delete event.
Why do you need a dummy record? Probably a simpler approach.

 

[MajP]

Another thing. The form's current event happens whenever you change records. Here you can modify the allow deletions, edits, and additions properties. So based on some value of the record, you can turn on or off these values

Me.allowdeletions = (not me.id = 1234)

 

[Klepdog]

so if the value of the field is null then it would be me.allowdeletion =not (isnull(me.searchstock) and isnull(me.searchpart))?

 

[MajP]

That says you can delete only if there are values in both fields.