0

I'm new to VBA and am trying to get a macro I'm writing to check if there is already a pre-existing record corresponding to the date/shift combination of the new record to be added. To do so, I'm trying to use DLookup to search through the database and only allow the new record if DLookup returns a Null, which should happen unless it finds a record which has both the date and shift matching the new data (I'm using a composite primary key using these two pieces of data). Alas, as the title says, I keep getting run-time error 424 and when I debug it highlights

lookTest = Access.DLookup("Shift", "trialTable", "[Date of Production]=#" & shiftDate & "#" & "And [Shift]='" & currentShift & "'")

as the problem line. ("Shift" and "Date of Production" are the column headings for the two columns used as the composite key, "trialTable" is the table I'm working with, "shiftDate" is a date and "currentShift" is a string) As far as I can tell my syntax is correct, so I have no idea where I'm going wrong with this at this point (It's probably something dumb and obvious, I'm sure). If it helps, below is the rest of the code leading up to that point in case I've screwed something up earlier on and the computer just isn't realizing it until it hits that line. Any assistance is greatly appreciated, thanks!

Sub DatabaseUpdate()
' exports data from the active spreadsheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Ace.OLEDB.12.0; " & "Data Source=H:\TestingDatabase.accdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "trialTable", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records on a table
    With rs
        .AddNew 'create a new record

        'Ask the user which shift the report is for.
        Dim currentShift As String
        Dim shiftDate As Date
        Dim shiftCheck As Boolean
        shiftCheck = False

        ' Request and check if the user has entered in a valid shift. If so, continue. If not, inform them and then repeat the request for the shift identity.
        Do While shiftCheck = False
            currentShift = UCase(InputBox("Which shift is this entry for? Please input only A, B, or C. (not case-sensitive)"))
            If currentShift = "A" Or currentShift = "B" Or currentShift = "C" Then
                shiftCheck = True
            Else
                wrongLetterWarning = MsgBox("Sorry, that is not an accepted response (A, B, or C). Please try again.", vbOKOnly)
            End If
        Loop

        ' Request the date the shift occured on. MM/DD/YYYY format is important as the date and shift together form the database's primary key.
        shiftDate = InputBox("On which date did this shift occur? Please use MM/DD/YYYY format.")

        'Check to make sure that there isn't already a pre-existing record for the date/shift combination.
        Dim lookTest As Variant
        lookTest = Access.DLookup("Shift", "trialTable", "[Date of Production]=#" & shiftDate & "#" & "And [Shift]='" & currentShift & "'")
braX
  • 11,506
  • 5
  • 20
  • 33
Grahammophone
  • 1
  • 1
  • What is `Access`? – GSerg Nov 25 '19 at 23:55
  • Turn on `Option Explicit` and see what happens when you compile it. – braX Nov 26 '19 at 00:17
  • @GSerg: MS Access @ braX: First it made me specify the variable wrongLetterWarning as a variant, and now it is flagging Access as a variable for some reason (it isn't one) and saying that it is undefined. – Grahammophone Nov 26 '19 at 00:27
  • 1
    Of course it is undefined. There is no such thing as `Access` in the MS Access object model. There is `Application` though, and it does have `DLookUp` as its method. – GSerg Nov 26 '19 at 00:43
  • @Grahammophone Is that VBA code contained in an Excel project or an Access project? The code comment in `DatabaseUpdate` mentions "excel spreadsheet". – HansUp Nov 26 '19 at 00:49
  • @GSerg: Ya, I think this was a result of me misunderstanding a posted solution to another problem I was having earlier with VBA not recognizing DLookup as a valid function. I read it as Application.DLookup where Apllication was a stand-in for whatever non-excel application you're trying to run the program on, rather than the actual command to use. I've now found and activated the right reference so I can just write DLookup() now, and that seems to have solved both problems. Now to figure out the next runtime 424 error that's popping up later in the code. The fun never stops with debugging, eh – Grahammophone Nov 26 '19 at 00:54
  • @HansUp: I don't know the difference. The macro is designed to create records in an Access database which is populated with data pulled from specific cells in an excel spreadsheet. I can open the VBA editor from either Excel or Access. – Grahammophone Nov 26 '19 at 00:56
  • Need to determine if this procedure is in Excel code module or Access code module. If this is in Access, there is no need for `Application` prefix. However, looks like procedure is in Excel module. Aside from any other issues, there is a syntax error. Need a space in front of `And`. Simplify concatenation `=#" & shiftDate & "# And [`. But if you already have current shift, why do you need to lookup Shift? – June7 Nov 26 '19 at 01:32
  • Does this answer your question? [How to query Microsoft Access Database fields from VBA in Excel](https://stackoverflow.com/questions/2002672/how-to-query-microsoft-access-database-fields-from-vba-in-excel). Actually, `Access` is a defined object if you set the Access library in Excel VBA. – June7 Nov 26 '19 at 01:50
  • @June7 I don't need to look up the Shift per se. It could return literally any value other than a Null when it finds a pre-existing record. I just happened to choose to tell it to return the Shift entry. And no, that linked page doesn't have what I'm looking for. I've also managed to get rid of the Application prefix through adding a reference. Thanks though! – Grahammophone Nov 26 '19 at 02:11

1 Answers1

1

I tested DLookup in Excel and it found table if database is physically open. Since you have a connection to database and already open a recordset, apply filter to recordset.

'code to collect data
...
rs.Open "SELECT * FROM trialTable WHERE [Date of Production]=#" & shiftDate & "# And [Shift]='" & currentShift & "'", cn, adOpenKeyset, adLockOptimistic
If rs.EOF And rs.BOF Then
    'code to create record
    ...
Else
    MsgBox "Record already exists."
End If
June7
  • 19,874
  • 8
  • 24
  • 34