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

Discuss Editing Data in Code AND on Form

Abhean
P: 24
[Admin (NeoPa) Addition]
This new thread was created from an existing one (Adding another search/modify to DAO recordset search) as the discussion was not related to the question of the thread.
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!
NeoPa, sorry I removed (commented out) lines 9 and 10 from above, so it is now just opening the modification form. Just using the search to see if there is a record already there or if one need to be added.

Zmbd, please elaborate. I was nervous at doing the second "OpenRecordset" veritable but wasn't sure of any other way. Was hoping that the ".Close" at the end of the first "With" closed down "rst" OpenRecordset.

If you cant tell I am relitive new to this counting my 1 semester of database. :)

Within the code I needed (to start with) a way to check and add a record into my "Roche" table for a QR code when the user scanned it in.

Then I need the code to check another table using a different variable that has already been stripped from the QR code to check if it is already in the "MaterialNum" table. If Not found I open a form to modify all the fields in that record and that form saves the info onto the table. Info is passed to the from via "TempVars".

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

✓ answered by zmbd

Abhean,
Please accept my apologies for keeping you waiting
Large family, cracked windshield in the truck, etc... normal life stuff ;-)

At the risk of going way off topic here, I think it's worth taking a look at your code to make sure that what you're asking here doesn't get you in trouble - we can always split the thread if needed :) :

(+)
Line 41, 45, 48, 57, etc....
It's not common to use the Call statement before the method-property of an instituted object. Instead the Call Statement is used for transferring control to a procedure such as a Sub, Function, and/or external code such as a DLL, Powershell, or Batch file.
While the VBA-IDE doesn't choke on the construct you have - just something to be aware of if Microsoft changes its mind.

(+)
Line 82 : DO NOT CLOSE YOUR DATABASE OBJECT!
In my early days I cannot tell you how many times I did this and then had errors relating to the database.
>Rule of thumb:
If you set it you clear it
If you open it you close it.

I would also change the code between lines 81 and 85 so that you release objects in LIFO order to reduce the chance of orphaning records or leaking memory:
Expand|Select|Wrap|Line Numbers
  1. ' Check that all of your record sets are closed and memory released
  2. If not rst is nothing then
  3.  rst.close
  4.  set rst = nothing
  5. end if
  6. '
  7. If not rst1 is nothing then
  8.  rst1.close
  9.  set rst1 = nothing
  10. end if
  11. ' now that all of the dependencies are clear release the memory for the pointer to the database
  12. if not db is nothing then set db = nothing
(+)
Now we get to Line 71
You've commented out Lines 69, 70 - good, have your called form handle the new record creation.
In Post#4 these were lines 9 and 10
Why did I think this was a disaster waiting to happen in post#4?
- You had an active recordset "RST1"
- You then created an optimistic lock on "MaterilNum"
- You then open a form, that I am guessing, attempts to edit records in "Roche" and/or "MaterialNum" with possibly some dependency between these two.
-- At some point, Access may switch from the optimistic record lock in RST1 to an active lock (IDK, Access occasionally does weird things - I have an Oracle database that underlies one of the instruments in my lab that throws weird errors and neither Oracle nor the developer can find the root cause @?@)
At best case when this happens the code fails, you have an orphan record, and you can recover. The worse case is that your recordset becomes corrupted, corrupting the underlying tables... and you'll be reaching out to us to help, and we'll commiserate with you, as we have all been in that same position at some-point, and try to get as much of your data back as possible :)

>> How do I know this may happen...
Because I inherited a database that had a very similar construct - and it failed spectacularly!
(Not the worse database I've inherited, the worse is still in use and the person who developed it designed it like an Excel workbook - see Z pulling hair out and foaming at the mouth because the users will not let me change it!)

As I continue thru the code...
It looks like you are only using RST1 for a simple search. Simple searches like this may be the one time where I will use DlookUp especially if the value I am returning/checking is a "one off" (that is, I'm not searching dozens of things at one go) and has a unique reference, such as an instrument serial number or primary key, associated with it - this is because DLookUP will only return the first record it finds that matches the criteria.

If you do want to use the DlookUp You might consider using a construct similar to the following... I've broken this code down in to tiny parts so that you can see what is happening and to make it easy for you to play with in your database, of course, one would simplify the code a bit :).
Two things I would do:
Build your criteria string outside of the Dlookup as I've done on line 9
Same thing with Line 14
This will make it easier to troubleshoot the code should something go funky.
(yes, you can have a parameter function that you can pass values to that handles the strings, numeric, etc and returns true false... I do, and it makes the code cleaner to the eye :) )
Expand|Select|Wrap|Line Numbers
  1. Sub POC20190525()
  2.   Dim zExpr As String
  3.   Dim ZDomain As String
  4.   Dim zCriteria As String
  5.   Dim zMissing As Boolean
  6.  
  7.   zExpr = "[FieldNameToReturnValueFrom]"
  8.   ZDomain = "YourTableName"
  9.   zCriteria = "[FieldToSearch]=1"
  10.   'Be aware that if the value you are searchin is in string/text value then you will need something like
  11.   'zCriteria = "[FieldToSearch]='TextString'"
  12.   'Neopa uses the replace function with great success in these cases to place the desired quotes.
  13.  
  14.   zMissing = ("" & DLookup(expr:=zExpr, domain:=ZDomain, criteria:=zCriteria)) = ""
  15.  
  16.   If zMissing Then MsgBox prompt:=zCriteria & vbCrLf & "Was not found in" & vbCrLf & ZDomain
  17.  
  18. End Sub
Keep in mine that line 86, Me.Refresh may not pick up new records for the form's recordset, especially if you have subforms or use a secondary form to add records. You may want to use me.requery - disadvantage is that you may loose your place in the recordset on the form. There are workarounds for this should you need them.

Line 90, is this another open form or the form within which this code resides? If the later then you should be able to simplify the code to Me.txtUPCsearch.setfocus

Just a thought, you should consider prefacing your variable names with "my" or something along that line. It will keep you away from reserved words and tokens. For example, "Name" is a reserved word/token depending on the context; thus, even in a table field name I will use something like [EmplyFName] or [FName] and in a variable something like Dim MyDB As DAO.Database. As a rule of thumb I don't use an underscore as the first character as this might also cause issues depending on the context.

I hope I've answered your question and maybe a bit more?
-z

Share this Question
Share on Google+
6 Replies


zmbd
Expert Mod 5K+
P: 5,397
Abhean,
Please accept my apologies for keeping you waiting
Large family, cracked windshield in the truck, etc... normal life stuff ;-)

At the risk of going way off topic here, I think it's worth taking a look at your code to make sure that what you're asking here doesn't get you in trouble - we can always split the thread if needed :) :

(+)
Line 41, 45, 48, 57, etc....
It's not common to use the Call statement before the method-property of an instituted object. Instead the Call Statement is used for transferring control to a procedure such as a Sub, Function, and/or external code such as a DLL, Powershell, or Batch file.
While the VBA-IDE doesn't choke on the construct you have - just something to be aware of if Microsoft changes its mind.

(+)
Line 82 : DO NOT CLOSE YOUR DATABASE OBJECT!
In my early days I cannot tell you how many times I did this and then had errors relating to the database.
>Rule of thumb:
If you set it you clear it
If you open it you close it.

I would also change the code between lines 81 and 85 so that you release objects in LIFO order to reduce the chance of orphaning records or leaking memory:
Expand|Select|Wrap|Line Numbers
  1. ' Check that all of your record sets are closed and memory released
  2. If not rst is nothing then
  3.  rst.close
  4.  set rst = nothing
  5. end if
  6. '
  7. If not rst1 is nothing then
  8.  rst1.close
  9.  set rst1 = nothing
  10. end if
  11. ' now that all of the dependencies are clear release the memory for the pointer to the database
  12. if not db is nothing then set db = nothing
(+)
Now we get to Line 71
You've commented out Lines 69, 70 - good, have your called form handle the new record creation.
In Post#4 these were lines 9 and 10
Why did I think this was a disaster waiting to happen in post#4?
- You had an active recordset "RST1"
- You then created an optimistic lock on "MaterilNum"
- You then open a form, that I am guessing, attempts to edit records in "Roche" and/or "MaterialNum" with possibly some dependency between these two.
-- At some point, Access may switch from the optimistic record lock in RST1 to an active lock (IDK, Access occasionally does weird things - I have an Oracle database that underlies one of the instruments in my lab that throws weird errors and neither Oracle nor the developer can find the root cause @?@)
At best case when this happens the code fails, you have an orphan record, and you can recover. The worse case is that your recordset becomes corrupted, corrupting the underlying tables... and you'll be reaching out to us to help, and we'll commiserate with you, as we have all been in that same position at some-point, and try to get as much of your data back as possible :)

>> How do I know this may happen...
Because I inherited a database that had a very similar construct - and it failed spectacularly!
(Not the worse database I've inherited, the worse is still in use and the person who developed it designed it like an Excel workbook - see Z pulling hair out and foaming at the mouth because the users will not let me change it!)

As I continue thru the code...
It looks like you are only using RST1 for a simple search. Simple searches like this may be the one time where I will use DlookUp especially if the value I am returning/checking is a "one off" (that is, I'm not searching dozens of things at one go) and has a unique reference, such as an instrument serial number or primary key, associated with it - this is because DLookUP will only return the first record it finds that matches the criteria.

If you do want to use the DlookUp You might consider using a construct similar to the following... I've broken this code down in to tiny parts so that you can see what is happening and to make it easy for you to play with in your database, of course, one would simplify the code a bit :).
Two things I would do:
Build your criteria string outside of the Dlookup as I've done on line 9
Same thing with Line 14
This will make it easier to troubleshoot the code should something go funky.
(yes, you can have a parameter function that you can pass values to that handles the strings, numeric, etc and returns true false... I do, and it makes the code cleaner to the eye :) )
Expand|Select|Wrap|Line Numbers
  1. Sub POC20190525()
  2.   Dim zExpr As String
  3.   Dim ZDomain As String
  4.   Dim zCriteria As String
  5.   Dim zMissing As Boolean
  6.  
  7.   zExpr = "[FieldNameToReturnValueFrom]"
  8.   ZDomain = "YourTableName"
  9.   zCriteria = "[FieldToSearch]=1"
  10.   'Be aware that if the value you are searchin is in string/text value then you will need something like
  11.   'zCriteria = "[FieldToSearch]='TextString'"
  12.   'Neopa uses the replace function with great success in these cases to place the desired quotes.
  13.  
  14.   zMissing = ("" & DLookup(expr:=zExpr, domain:=ZDomain, criteria:=zCriteria)) = ""
  15.  
  16.   If zMissing Then MsgBox prompt:=zCriteria & vbCrLf & "Was not found in" & vbCrLf & ZDomain
  17.  
  18. End Sub
Keep in mine that line 86, Me.Refresh may not pick up new records for the form's recordset, especially if you have subforms or use a secondary form to add records. You may want to use me.requery - disadvantage is that you may loose your place in the recordset on the form. There are workarounds for this should you need them.

Line 90, is this another open form or the form within which this code resides? If the later then you should be able to simplify the code to Me.txtUPCsearch.setfocus

Just a thought, you should consider prefacing your variable names with "my" or something along that line. It will keep you away from reserved words and tokens. For example, "Name" is a reserved word/token depending on the context; thus, even in a table field name I will use something like [EmplyFName] or [FName] and in a variable something like Dim MyDB As DAO.Database. As a rule of thumb I don't use an underscore as the first character as this might also cause issues depending on the context.

I hope I've answered your question and maybe a bit more?
-z
May 25 '19 #2

NeoPa
Expert Mod 15k+
P: 31,426
Yeah. Off topic.

It's very unusual for a thread with an appropriate answer to need any further technical discussion. If such is required then it's a different topic & different thread and you can expect a moderator to move it for you.

Obviously, and ideally, create the new thread yourself and simplify everything :-)

This IS an interesting question and deserves to be seen. Hiding it in a thread for another question would ensure it's mainly overlooked.
May 25 '19 #3

zmbd
Expert Mod 5K+
P: 5,397
Of Course it was :)

I wanted to make sure that OP had a chance to see the post(s) before splitting them out to a new thread. Thank you for taking the time to do so for me!
May 26 '19 #4

NeoPa
Expert Mod 15k+
P: 31,426
I'm starting to see why a Like button would be helpful here ;-)

All good. I'm glad this point was raised and is a thread that can be found.
May 26 '19 #5

Abhean
P: 24
@zmbd

Thanks for your input! I am grateful for your time and experience. Everything makes since and I am following your advice.

@NeoPa sorry for going off the main topic. Simply trying to gain as much as I can. :)
May 28 '19 #6

NeoPa
Expert Mod 15k+
P: 31,426
We encourage your thirst and your search.

We moderators merely want to guide you as to the best use of those resources. Best in that it keeps threads clean and easy to understand for you, as well as separate and distinct (& clean & easy to understand too of course) for those searching for help to follow in your footsteps and find their way easily.

You can bet for every question you ask there are hundreds who will want those same answers. If you're ever unconvinced of that check out the count of Views in the list. Just look at your own threads (Threads Started By: Abhean) to see there have been a total of 3,463 for just those six threads already - and you only signed up in March. That's less than three months.

You can see why it's so important that our content is arranged in a simple and natural way. Most of our traffic is actually from non-members (& members of course) just searching for answers.
May 28 '19 #7

Post your reply

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