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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-22-2019, 06:04 AM   #16
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 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

PatAccess is offline   Reply With Quote
Old 05-22-2019, 06:32 AM   #17
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,584
Thanks: 387
Thanked 618 Times in 599 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Record.AddNew

You really should check if any records are returned before the rest of the processing?
__________________
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
Old 05-22-2019, 06:41 AM   #18
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,552
Thanks: 29
Thanked 631 Times in 614 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Record.AddNew

Quote:
Originally Posted by PatAccess View Post
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.

__________________
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-22-2019, 09:39 AM   #19
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,774
Thanks: 76
Thanked 1,532 Times in 1,421 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

Quote:
When you select one row from listbox1 it generates a row in listbox2 which is associated with the [Cert] field.
Quote:
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

__________________
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
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 03:21 PM.


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