Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    67

    Unanswered: Runtime error 3078 - cannot find input table or query "True"

    I am getting the runtime error 3078. I am using the DCOUNT function to get a count of records that match the users request. The following code is:

    Dim strCounter As Long
    strCounter = DCount("download_log", [download_log.job_number] = Forms!download_log_form!JOB_NUMBER)

    If strCounter > 0 Then
    MsgVariable = MsgBox("Record Already Exist")
    End If


    I have also tried the following code, but I get syntax errors:

    RS.Open "SELECT count(*) into '" & strCounter & "' FROM DOWNLOAD_LOG WHERE DOWNLOAD_LOG.JOB_NUMBER = Forms!download_log_form!job_number"

    If strCounter > 0 Then
    MsgVariable = MsgBox("Record Already Exist")
    End If


    Any suggestions. What am I doing wrong?

    Thank you in advance!

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    I am getting the runtime error 3078. I am using the DCOUNT function to get a count of records that match the users request. The following code is:

    Dim strCounter As Long
    strCounter = DCount("download_log", [download_log.job_number] = Forms!download_log_form!JOB_NUMBER)

    If strCounter > 0 Then
    MsgVariable = MsgBox("Record Already Exist")
    End If
    try:

    Code:
    
    Dim strCounter As Long
    strCounter = DCount("download_log", "[download_log.job_number] =" &  Forms!download_log_form!JOB_NUMBER)
    
    If strCounter > 0 Then
       MsgVariable = MsgBox("Record Already Exist")
    End If
    If Job_number is a text data type try:

    Code:
    
    Dim strCounter As Long
    strCounter = DCount("download_log", "[download_log.job_number] =" & Chr(34)  &  Forms!download_log_form!JOB_NUMBER & Chr(34) )
    
    If strCounter > 0 Then
       MsgVariable = MsgBox("Record Already Exist")
    End If



    Quote Originally Posted by eva.thaeh View Post
    I have also tried the following code, but I get syntax errors:

    RS.Open "SELECT count(*) into '" & strCounter & "' FROM DOWNLOAD_LOG WHERE DOWNLOAD_LOG.JOB_NUMBER = Forms!download_log_form!job_number"

    If strCounter > 0 Then
    MsgVariable = MsgBox("Record Already Exist")
    End If


    Any suggestions. What am I doing wrong?

    Thank you in advance!
    With a recordset, you can not use form references embedded in the SQL, You must substitute the value

    Try:
    Code:
    Dim lngCounter As Long
    
    
    RS.Open "SELECT count(*) as LogCount FROM DOWNLOAD_LOG WHERE DOWNLOAD_LOG.JOB_NUMBER = " & Forms!download_log_form!job_number
    
    lngCounter = rs!LogCount
    If Job_number is a text data type try:

    Code:
    
    Dim lngCounter As Long
    
    RS.Open "SELECT count(*) as LogCount  FROM DOWNLOAD_LOG WHERE DOWNLOAD_LOG.JOB_NUMBER = " & Chr(34) & Forms!download_log_form!job_number & Chr(34) & ";"
    
    lngCounter = rs!LogCount

    Additional Notes:

    Dim strCounter As Long


    The prefix of str is normally used to indicate the the data type is string.

    For the long data type use the prefix lng

    Dim lngCounter As Long


    The key is to use a naming convention correctly and consistently.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2010
    Posts
    67
    Thank you so much HiTechCoach.

    I am new to VB, I have a mainframe background, so please excuse my simple questions.

    The suggested DCOUNT function code is working because it is acknowledging the user input on the form. However, I am missing something because I now get the following:

    Run-time error '3078':
    The Microsoft Access database engine cannot find the input table or query '[dowload_log.job_number] = 45632' . Make sure it exists and that its name is spelled correctly.

    This is a general question.

    Do I have to have an explicit "open tables and/or record sets" statement in my code?

    Thank you in advance!

  4. #4
    Join Date
    May 2010
    Posts
    601
    Try:

    Code:
    
    Dim lngCounter As Long
    
    lngCounter = DCount("*", "download_log", "[job_number] =" &  Forms!download_log_form!JOB_NUMBER)
    
    If lngCounter > 0 Then
       MsgVariable = MsgBox("Record Already Exist")
    End If
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Boyd did the same thing on his first attempt with DCount() as you did, eva! He omitted the second argument in the function, Domain, which tells Access which table or query to look at! His last post should work, assuming [job_number] is defineed as a Number. IF it's defined as Text, look at the Where clause in his original post for the Text syntax.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •