Record.AddNew (1 Viewer)

PatAccess · May 16, 2019

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

theDBguy · May 16, 2019

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...

The_Doc_Man · May 16, 2019

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.

Petr Danes · May 16, 2019

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.

Petr Danes · May 16, 2019

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

PatAccess · May 16, 2019

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

Petr Danes · May 16, 2019

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.

PatAccess · May 17, 2019

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?

The_Doc_Man · May 17, 2019

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/office/vba/api/access.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.

Petr Danes · May 17, 2019

PatAccess said:
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?

PatAccess · May 21, 2019

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

gemma-the-husky · May 21, 2019

Code:

strDoc = strFolder + strFile

should be

Code:

strDoc = strFolder [B][COLOR="Red"]&[/COLOR][/B] strFile

assuming strFolder has a trailing backslash.

That's probably why you are getting a blank.

The_Doc_Man · May 21, 2019

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.

PatAccess · May 22, 2019

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.

Gasman · May 22, 2019

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.

PatAccess · May 22, 2019

Hello Guys I got it to work. I change my Recordset to:

Code:

strSQL = "SELECT Cert FROM Tbl_EngineerLic WHERE LicNum = '" & Me.ListBoxStateLic.Column(1) & "' AND Cert IS NULL"

So here is the full code.

Code:

' Add a new certificate filepath
Private Sub cmdViewCert_Click()
Dim objCert As Object
Dim strSQL As String
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


Set db = CurrentDb
strSQL = "SELECT Cert FROM Tbl_EngineerLic WHERE LicNum = '" & Me.ListBoxStateLic.Column(1) & "' AND Cert IS NULL"
'Open my Recordset with the SQL criteria and make it editable
Set rs = db.OpenRecordset(strSQL, 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 dialog object
    If objCert.Show Then
        For Each varItem In objCert.SelectedItems
        strFile = Dir(varItem)
        strFolder = Left(varItem, Len(varItem) - Len(strFile))
        MsgBox "You are adding Folder:" & vbNewLine & 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
        MsgBox "Add the new certificate!"
        rs.Edit
        'MsgBox "[" & rs.Fields("Cert") & "]" & vbCrLf & "[" & Me.ListBoxStateLic.Column(3) & "]"
        'rs.AddNew
        rs![Cert] = strDoc
       
        rs.Update

        Next
    End If

rs.Close

Set objCert = Nothing
Set rs = Nothing
db.Close

End Sub

Thank you for your patience

Gasman · May 22, 2019

You really should check if any records are returned before the rest of the processing?

theDBguy · May 22, 2019

PatAccess said:
Hello Guys I got it to work. I change my Recordset to:

Code:

strSQL = "SELECT Cert FROM Tbl_EngineerLic WHERE LicNum = '" & Me.ListBoxStateLic.Column(1) & "' AND Cert IS NULL"

...
Thank you for your patience

Hi. Congratulations! Glad to hear you got it sorted out.

The_Doc_Man · May 22, 2019

When you select one row from listbox1 it generates a row in listbox2 which is associated with the [Cert] field.

it does not add that into the "Cert" field and I don't understand why.

If this [Cert] field is displayed via ListBox, the fields of the listbox are READ-ONLY. You CANNOT update them on-the-fly. You have to directly update the underlying .RowSource and then .Requery the listbox to see changes.

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

Record.AddNew (1 Viewer)

Registered User.

I’m here to help

Immoderate Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Immoderate Moderator

Registered User.

Registered User.

Super Moderator

Immoderate Moderator

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

I’m here to help

Immoderate Moderator

Similar threads

Users who are viewing this thread