By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Adding another search/modify to DAO recordset search

Abhean
P: 24
With Twinnyfo's help I was able to get a record search up and running. Now, I need some more help. How can I search on a second table either during or after my current search.

Once the main search in the "Roche" table I need to do another search in the "MaterialNum" table. Any other help or pointers on the following code would be most appreciated.

Expand|Select|Wrap|Line Numbers
  1. Private Sub TxtUpcSearch_AfterUpdate()
  2.     Dim BaseUPC As String
  3.     Dim BaseRef As String
  4.     Dim FinishedRef As String
  5.  
  6.     'Pull the UPC
  7.     BaseUPC = Me.txtUPCSearch
  8.     'Set BaseRef like on main table
  9.     BaseRef = Right(BaseUPC, 11)
  10.     'rip the leading 0 off of the BaseRef
  11.     FinishedRef = Fix(BaseRef)
  12.     'show
  13.     Me.FinalRef = FinishedRef
  14.  
  15.     'set reference number
  16.     'step one Stip extra characters
  17.     ref0 = Right(Me.txtUPCSearch, 11)
  18.     'step two Make sure if leading 0, it is stripped
  19.     ref1 = Fix(ref0)
  20.     'Adding Var to project-wide access
  21.     TempVars.Add "ref3", (ref1)
  22.     'testing text box for Var check
  23.     Text23 = TempVars!ref3
  24.  
  25.    Dim db      As DAO.Database
  26.     Dim rst     As DAO.Recordset
  27.     Set db = CurrentDb()
  28.     Set rst = db.OpenRecordset("Roche", dbOpenDynaset)
  29.  
  30.     With rst
  31.         If Not (.BOF And .EOF) Then
  32.             Call .MoveFirst
  33.             .FindFirst "OrgUPC = '" & Me.txtUPCSearch & "'"
  34.             If Not .NoMatch Then
  35.                 'Found it!
  36.                 Call .Edit
  37.                 !OnHand = !OnHand + 1
  38.                 !RocRecvd = Me.Date
  39.                 Call .Update
  40.             Else
  41.                 'Didn't find it
  42.                 Call .AddNew
  43.                 !OrgUPC = Me.txtUPCSearch
  44.                 !OnHand = 1
  45.                 !RocRecvd = Me.Date
  46.                 'Make sure Correct size for the record (48 characters)
  47.                     If BaseUPC Like "################################################" Then
  48.                         Call .Update
  49.                     Else
  50.                         'Not
  51.                         MsgBox "The QR code is not valid, Please verify and Re-Scan Item", 0, "Item Error..."
  52.                     End If
  53.             End If
  54.         End If
  55.         Call .Close
  56.     End With
  57.     Call db.Close
  58.     Set rst = Nothing
  59.     Set db = Nothing
  60.     'refresh screen
  61.     Me.Refresh
  62.     'set txtbox back to null
  63.     Me.txtUPCSearch = Null
  64.     'Set focus back on text box
  65.     Forms!RocheSingleEntry!txtUPCSearch.SetFocus
  66.  
  67. End Sub
4 Weeks Ago #1

✓ answered by NeoPa

Hi Abhean.

.AddNew() doesn't create a new record. It creates a buffer space in order for you to populate the Fields' data and then call .Update() to commit that data to the table. Without the second step nothing is added to the table (recordset).

Share this Question
Share on Google+
6 Replies


Abhean
P: 24
Ok, I think I am getting there.

Modified the above code to add a new var (rst1) and set that to look at my "MaterialNum" table.

Expand|Select|Wrap|Line Numbers
  1. Dim db      As DAO.Database
  2.     Dim rst     As DAO.Recordset
  3.     Dim rst1    As DAO.Recordset
  4.     Set db = CurrentDb()
  5.     Set rst = db.OpenRecordset("Roche", dbOpenDynaset)
  6.     Set rst1 = db.OpenRecordset("MaterialNum", dbOpenDynaset)
And then added the below after the main search. It appears to almost work. I does not appear to create the new record.

Expand|Select|Wrap|Line Numbers
  1.      Call .Close
  2.     End With
  3.     With rst1
  4.         If Not (.BOF And .EOF) Then
  5.         .FindFirst "MaterialNumber= '" & Me.FinalRef & "'"
  6.         If Not .NoMatch Then
  7.             'found it
  8.             MsgBox "found the record"
  9.         Else
  10.             'Not there
  11.             Call .AddNew
  12.             !MaterialNumber = Me.FinalRef
  13.             MsgBox "it has been added"
  14.         End If
  15.         End If
  16.         Call .Close
  17.     End With
  18.     Call db.Close
4 Weeks Ago #2

NeoPa
Expert Mod 15k+
P: 31,347
Hi Abhean.

.AddNew() doesn't create a new record. It creates a buffer space in order for you to populate the Fields' data and then call .Update() to commit that data to the table. Without the second step nothing is added to the table (recordset).
4 Weeks Ago #3

Abhean
P: 24
Many thanks. Had just figured out the update, But decided to open a form based on the MaterialNumber so the user could fill out the entire record on that table. Looks like:

Expand|Select|Wrap|Line Numbers
  1. With rst1
  2.             If Not (.BOF And .EOF) Then
  3.                 Call .MoveFirst
  4.                 .FindFirst "MaterialNumber= '" & Me.FinalRef & "'"
  5.                 If Not .NoMatch Then
  6.                     'nothing needed
  7.                 Else
  8.                     'Not there
  9.                     Call .AddNew
  10.                     !MaterialNumber = Me.FinalRef
  11.                     DoCmd.OpenForm "RocheMatEditFrm" 'opens form for complete record
  12.                 End If
  13.             End If
  14.             Call .Close
  15.         End With
4 Weeks Ago #4

NeoPa
Expert Mod 15k+
P: 31,347
I'm a little confused. That doesn't appear to include the call to .Update() either. Clearly that code, as it stands, would suffer from the same problem.
4 Weeks Ago #5

zmbd
Expert Mod 5K+
P: 5,387
Abhean -
If I am following your code correctly... I highly advise AGAINST using both the code and the form to edit an open recordset from "both sides" of the record - you're begging for the database to implode!
4 Weeks Ago #6

NeoPa
Expert Mod 15k+
P: 31,347
ZMBD:
Abhean -
If I am following your code correctly... I highly advise AGAINST using both the code and the form to edit an open recordset from "both sides" of the record - you're begging for the database to implode!
This latest comment has led to a new thread (Discuss Editing Data in Code AND on Form) being created to discuss in more detail.
4 Weeks Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.