Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-16-2019, 12:07 PM   #1
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 147
Thanks: 103
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Record.AddNew

Hello Guys,
I have a form with several different controls in it. Right now, I am trying to get a command button to allow the user to:
open a dialog box
choose a file
and then edit my recordset to add the new filepath to a field.
The thing is at the moment, nothing is adding. Can you take a look at this code and tell me what I am doing wrong?

Thank you

' Add a new certificate filepath
Private Sub cmdViewCert_Click()
Dim objCert As Object
Dim strFile As String
Dim strFolder As String
Dim strDoc As String
Dim varItem As Variant

Dim db As DAO.Database
Dim rs As Recordset

'Open my Recordset and make it editable
Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_EngineerLic", dbOpenDynaset)

Set objCert = Application.FileDialog(3) 'Open the dialog box to choose the certificate
objCert.allowMultiSelect = True 'Allows the user to select multiple file from the file dialog box.

'Create my object
If objCert.Show Then
For Each varItem In objCert.SelectedItems
strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder" & strFolder & vbCrLf & "File: " & strFile
strDoc = strFolder + strFile

' If the opened Cert field is equal to the one selected in the listBox
'then the value of that field = to the new filepath
If rs.Fields("Cert") = Me.ListBoxStateLic.Column(3) Then
rs.Edit
rs.Fields("Cert") = strDoc
rs.AddNew
rs.Update
End If

Next
End If

rs.Close

Set objCert = Nothing
Set rs = Nothing
db.Close

End Sub

PatAccess is offline   Reply With Quote
Old 05-16-2019, 12:11 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,709
Thanks: 33
Thanked 649 Times in 632 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Record.AddNew

Hi. I think you don't need .Edit and just use .AddNew. For example:
Code:
rs.AddNew
rs.Fields("Cert") = strDoc
rs.Update
Hope it helps...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-16-2019, 12:43 PM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,822
Thanks: 78
Thanked 1,539 Times in 1,427 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Record.AddNew

Agree with theDBguy. In fact, that .Edit is probably confusing the issue because you have a dangling open record (from the .Edit) that is superseded by the .AddNew and I don't know what that will do.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-16-2019, 12:50 PM   #4
Petr Danes
Newly Registered User
 
Join Date: Aug 2010
Posts: 45
Thanks: 0
Thanked 1 Time in 1 Post
Petr Danes is on a distinguished road
Re: Record.AddNew

I'm surprised that doesn't throw an error. Seems to me it should, but I just tried it myself and it doesn't.

In any case, the .AddNew is what is throwing you off. You want to use EITHER .Edit, for altering an existing record (which you must first locate), OR .AddNew to start a new record. Then you do whatever manipulations you need in the record, and as a last step, .Update will write the record, new or changed, back into the table.

This:
Code:
rs.AddNew
rs.Fields("Cert") = strDoc
rs.Update
OR this:
Code:
rs.Edit
rs.Fields("Cert") = strDoc
rs.Update
In your case, you started out correctly editing a record, by issuing the .Edit command, then you updated the field you wanted to change, then you cancelled that edit by using the .AddNew command, then the .Update command wrote out the newly created and completely empty record (providing there are not constraints in your table that prevent an empty record - in such a case, the .Update command should throw an error).

If you simply remove your line:
Code:
rs.AddNew
you should be fine.
__________________
This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the forums whenever possible, so that all may benefit from the exchange of ideas.
Petr Danes is offline   Reply With Quote
Old 05-16-2019, 12:55 PM   #5
Petr Danes
Newly Registered User
 
Join Date: Aug 2010
Posts: 45
Thanks: 0
Thanked 1 Time in 1 Post
Petr Danes is on a distinguished road
Re: Record.AddNew

Or maybe I misunderstood your need. Did you want to add information to an existing record, or add a completely new record? If the former, my first answer holds. If the latter, then you should do it this way:
Code:
rs.AddNew
rs.Fields("Cert") = strDoc
rs.Update
__________________
This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the forums whenever possible, so that all may benefit from the exchange of ideas.
Petr Danes is offline   Reply With Quote
The Following User Says Thank You to Petr Danes For This Useful Post:
Gasman (05-17-2019)
Old 05-16-2019, 01:07 PM   #6
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 147
Thanks: 103
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: Record.AddNew

Hello Petr Danes,
I would like to add information to an existing record. So I would like it to add that filepath to the field "Cert". I removed the rs.AddNew but it is still not adding that information in my table. I'm trying to understand why
so now I have this:
' Add a new certificate filepath
Private Sub cmdViewCert_Click()
Dim objCert As Object
Dim strFile As String
Dim strFolder As String
Dim strDoc As String
Dim varItem As Variant

Dim db As DAO.Database
Dim rs As Recordset

'Open my Recordset and make it editable
Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_EngineerLic", dbOpenDynaset)

Set objCert = Application.FileDialog(3) 'Open the dialog box to choose the certificate
objCert.allowMultiSelect = True 'Allows the user to select multiple file from the file dialog box.

'Create my object
If objCert.Show Then
For Each varItem In objCert.SelectedItems
strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder" & strFolder & vbCrLf & "File: " & strFile
strDoc = strFolder + strFile

' If the opened Cert field is equal to the one selected in the listBox
'then the value of that field = to the new filepath
If rs.Fields("Cert") = Me.ListBoxStateLic.Column(3) Then
rs.Edit
rs.Fields("Cert") = strDoc
rs.Update
End If

Next
End If

rs.Close

Set objCert = Nothing
Set rs = Nothing
db.Close

End Sub
PatAccess is offline   Reply With Quote
Old 05-16-2019, 01:29 PM   #7
Petr Danes
Newly Registered User
 
Join Date: Aug 2010
Posts: 45
Thanks: 0
Thanked 1 Time in 1 Post
Petr Danes is on a distinguished road
Re: Record.AddNew

Are you certain that your comparison is correct? The line:
Code:
If rs.Fields("Cert") = Me.ListBoxStateLic.Column(3) Then
will skip the entire recordset manipulation if there is not an exact match. I just tried your code and it worked fine, updating the table with no problem. My first suspicion would be that what is in your listbox does not exactly match what is in your table. I suggest you try adding this just before your comparison, to see exactly what is being compared.
Code:
MsgBox "[" & rs.Fields("Cert") & "]" & vbCrLf & "[" & Me.ListBoxStateLic.Column(3) & "]"
This will show the two texts above each other, with brackets around each case (to catch leading or trailing spaces). You could also add this
Code:
MsgBox "We're editing!"
just before the line
Code:
rs.Edit
to confirm that your code is entering the section for making the change.

__________________
This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the forums whenever possible, so that all may benefit from the exchange of ideas.
Petr Danes is offline   Reply With Quote
Old 05-17-2019, 04:19 AM   #8
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 147
Thanks: 103
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: Record.AddNew

Hi Petr Danes,
I just tried that it is not working.
The ListBox has 5 column so when the user select one, it brings up the certificate filepath in another listbox But if there is no value in that field, I want them to be able to add the filepath. This is my problem because it is not adding anything to the record. It print the filepath to be add but does not add anything so what am I missing or have wrong in my if statement? I just deleted that statement BUT how will it know to add the value to that specific record?
PatAccess is offline   Reply With Quote
Old 05-17-2019, 05:26 AM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,822
Thanks: 78
Thanked 1,539 Times in 1,427 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Record.AddNew

When I consider the properties of the list-box, the one that would be involved in updating the contents of the .RowSource would be the .Column(n,m) property. However, when I then look up .Column, it is listed as Read-Only.

https://docs.microsoft.com/en-us/off...listbox.column

I think you cannot update data directly in a listbox. The reason is that when you put that listbox in focus, it takes a momentary "snapshot" of what was potentially in the list so that another user can't "pull the rug out from under you" while you are making a choice. So it is a static list. That isn't to say that if you left the box and returned that you wouldn't get an updated list. It is only static for the time that it is in focus.

You COULD if, you wanted to do so, select the row to be updated (so you can get a selection index) and perhaps use a double-click event for that box to allow you to do a SEPARATE input via something as simple as an Input Box. Once you had the new value, you could pick up the PK of the selected record (which probably would have to have been part of the box anyway). Then you could use the .RowSource of that list box and build some type of recordset operation to do a .FindFirst on the key, .Edit the record, modify the field in question, and .Update the recordset (and of course close it). If you did that, you would have to do a .Requery of the listbox to see the updated value.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-17-2019, 11:47 AM   #10
Petr Danes
Newly Registered User
 
Join Date: Aug 2010
Posts: 45
Thanks: 0
Thanked 1 Time in 1 Post
Petr Danes is on a distinguished road
Re: Record.AddNew

Quote:
Originally Posted by PatAccess View Post
Hi Petr Danes,
I just tried that it is not working.
The ListBox has 5 column so when the user select one, it brings up the certificate filepath in another listbox But if there is no value in that field, I want them to be able to add the filepath. This is my problem because it is not adding anything to the record. It print the filepath to be add but does not add anything so what am I missing or have wrong in my if statement? I just deleted that statement BUT how will it know to add the value to that specific record?
Try putting a breakpoint on the the line
Code:
rs.Fields("Cert") = strDoc
and see what is in the field rs.Fields("Cert"), and in the variable strDoc. Then press F8 once, to execute that statement, and examine those two again. What is on those two storage locations before and after executing the statement?
__________________
This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the forums whenever possible, so that all may benefit from the exchange of ideas.
Petr Danes is offline   Reply With Quote
Old 05-21-2019, 08:34 AM   #11
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 147
Thanks: 103
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: Record.AddNew

Hi Petr Danes,
When I put the break, I see that the field rs.Fields("Cert") has the filepath I am trying to add in it and so does the variable but when I go to the table the value is not there.
I have also tried The_Doc_Man's suggestion to no avail.
Thank you for your help
PatAccess is offline   Reply With Quote
Old 05-21-2019, 08:43 AM   #12
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,749
Thanks: 55
Thanked 1,021 Times in 987 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Record.AddNew

Code:
strDoc = strFolder + strFile
should be

Code:
strDoc = strFolder & strFile
assuming strFolder has a trailing backslash.


That's probably why you are getting a blank.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-21-2019, 01:43 PM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,822
Thanks: 78
Thanked 1,539 Times in 1,427 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Record.AddNew

When you say "to no avail" - you do yourself a disservice. What happens that avails you nothing? Do you get an error message? Does Access crash on you? Does Windows crash on you? Do you get a blue screen of death? Do you get an emoticon that sticks its tongue out at you?

We need something as a symptom to diagnose. It is like you are going to a doctor and saying, "Doc, I don't feel good" ... and then stop right there and say nothing else. That could be anything from a mild tummyache to the aftermath of a Thanos snap.

Tell us what happens. Please. We WANT to help but need something to go on.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-22-2019, 04:08 AM   #14
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 147
Thanks: 103
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: Record.AddNew

I have a form with a textbox where I can type the name of a person and it generates 4 fields (with one hidden) about that person stored in listbox1.
When you select one row from listbox1 it generates a row in listbox2 which is associated with the [Cert] field.
If that field is blank I would like to give the user the opportunity to add a value (filepath-document) to that field via that command button.

This is the code that I have
Code:
' Add a new certificate filepath
Private Sub cmdViewCert_Click()
Dim objCert As Object
Dim strFile As String
Dim strFolder As String
Dim strDoc As String
Dim varItem As Variant

Dim db As DAO.Database
Dim rs As Recordset

'Open my Recordset and make it editable
Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_EngineerLic", dbOpenDynaset)

Set objCert = Application.FileDialog(3) 'Open the dialog box to choose the certificate
objCert.allowMultiSelect = True 'Allows the user to select multiple file from the file dialog box.

    'Create my object
    If objCert.Show Then
        For Each varItem In objCert.SelectedItems
        strFile = Dir(varItem)
        strFolder = Left(varItem, Len(varItem) - Len(strFile))
        MsgBox "Folder" & strFolder & vbCrLf & "File: " & strFile
        strDoc = strFolder + strFile

        ' If the opened Cert field is equal to the one selected in the listBox
        'then the value of that field = to the new filepath
        'If rs.Fields("Cert") = "" Then
        MsgBox "We're editing!"
        rs.Edit
        'MsgBox "[" & rs.Fields("Cert") & "]" & vbCrLf & "[" & Me.ListBoxStateLic.Column(3) & "]"
        'rs.AddNew
        rs.Fields("Cert") = strDoc
        'Me.ListBoxStateLic.Column(3) = strDoc
        'rs.AddNew
        
        rs.Update
        'End If

        Next
    End If

rs.Close

Set objCert = Nothing
Set rs = Nothing
db.Close

End Sub
It works up until
Code:
MsgBox "Folder" & strFolder & vbCrLf & "File: " & strFile
which displays the message box with the filepath but it does not add that into the "Cert" field and I don't understand why.
PatAccess is offline   Reply With Quote
Old 05-22-2019, 04:17 AM   #15
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,605
Thanks: 388
Thanked 622 Times in 603 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Record.AddNew

PMFJI,

Why not MSGBOX strDoc as that is what you are using?
With problems like this I always walk through the code line by line with F8 and check the variables.

__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using .AddNew with ADO but cannot update the new record immediately rileyjm Modules & VBA 7 02-14-2011 09:59 AM
New record into table with .AddNew Henley12 Tables 8 08-17-2009 01:08 PM
Creating New Record by addnew mmdonloaf Modules & VBA 6 09-06-2007 05:03 AM
Addnew only adds one record!! Lisad Queries 9 12-01-2005 03:22 AM
Addnew Record mboe Modules & VBA 1 12-15-2002 09:22 AM




All times are GMT -8. The time now is 01:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World