querying a table

[George]

Good Day all.



Can anyone explain what is wrong with this code?
It gives an error Message "Database engine cannot find the input table or query"
I am sure both are spelt correctly
When run with out the StrFilter it works fine; But I do not want to return the whole table.



Dim curDatabase As DAO.Database
Dim rs As DAO.Recordset
Dim strFilter As String


Set curDatabase = CurrentDb


strFilter = "officerName=" & Me.txtUser & " AND " & "[Auto Date] " & " = # " & DateValue(Now()) & "#"



Set rs = CurrentDb.OpenRecordset("Select * from [tblSignin],strfilter ")

[ranman256]

dont use code to run queries.
create the query in query design, save.
then the only code you need it: docmd.openquery "qsMyquery"

you dont need any code

[George]

Thanks. I am learning this fact for the first time; however there is a problem: the query is based on a value in a control on the form, hence the query has to be done after a value is entered into the control.

Second: I really believe that there is a need to return a recordset directly from the table because I want to change some of the values in the table base on an evaluation of the recordset.

Here's a look at my code:

Option Compare Database
Private Sub Command26_Click()
Dim curDatabase As DAO.Database
Dim rs As DAO.Recordset

Set curDatabase = CurrentDb

Set rs = CurrentDb.OpenRecordset("Select * from [tblSignin] ")

If Not rs.RecordCount = 0 Then

rs.MoveLast
rs.MoveFirst
End If
' MsgBox rs.RecordCount


rs.AddNew
rs.Fields(1) = Now()
rs.Fields(2) = Form_frmSignin.txtUser

rs.Fields(3) = Now()
rs.Fields(4) = Now()

rs.Fields(8) = Now()
rs.Fields(9) = "AutoSave"


'If number of records is even enter "IN" , if odd enter "OUT"
If rs.RecordCount Mod 2 = 1 Then

rs.Fields(6) = "IN"
Else: rs.Fields(6) = "OUT"
End If


rs.Update


Set curDatabase = Nothing

Set rs = Nothing
MsgBox "The time sheet has been submitted.", _
vbOKOnly Or vbInformation, "Kolo Bank"
Form_frmSignin.Visible = False
End Sub

[Micron]

I think your problem is simple. You have not concatenated your statement properly for text, but you thought of it for the date:
strFilter = "officerName= ' " & Me.txtUser & " ' AND " & "[Auto Date]" & " = # " & DateValue(Now()) & "#"
Remove the extra spaces I put in to make it more visible. Also, you don't need DateValue unless the variable is a string, not a date, and Now is a date. Be careful with this as it includes the time component, so it will only work for anything where [AutoDate] is equal to the given moment, which I fail to see how this is possible. Maybe you need Date()?
Nor do I see a need to parse your AND operator / [Auto Date] based on your post.
strFilter = "officerName= ' " & Me.txtUser & " ' AND [Auto Date] = # " & Now() & "#". In fact, depending on how you're using this, you usually don't need to wrap Now() or Date() in date delimiters.

Further to the first suggestion and your response, a query field can reference most data controls on an open form, so you don't have to set filters in code for many situations. But I'd never say 'never'.

[George]

Thanks for this thorough explanation. It really helps. Yes I mean Date rather than now() ; however I thought that if I wrap now() with DateValue that this would extract the date - a long unnecessary route of course.

The code did not run however but this maybe because I placed the strfilter in the Line like so:

Set rs = CurrentDb.OpenRecordset(strfilter)

Please advise how to modify. Should I have used the word where?

[Micron]

I see no reason to concatenate this when you can do it in one step, although I prefer to assign sql statements to variables. This allows me to check its validity by taking the variable output from the immediate window and pasting it into a test query sql view to test the result, especially if it's balking at the attempt while stepping through code. So miy approach would be to Dim sql as String and do the following:
sql = "Select * from [tblSignin] WHERE [officerName] = '" & Me.txtUser & "' AND [Auto Date] = Date"
Set rs = CurrentDb.OpenRecordset(sql)
A filter does not use the WHERE operator, but by trying to concatenate a filter with your SELECT portion, you omitted the WHERE clause. Again, no need to create these aspects separately then assemble them - unless you intend to use that filter more than once in separate steps but within the same procedure, which I think highly unlikely. My suggestion may not work since, as I said, I do not see how the procedure gets the value for [officerName]. I will take the liberty of commenting within your code, using block caps so that I don't have to use colour to make my text stand out. *means very important, otherwise, maybe just a suggestion:

*ALWAYS USE OPTION EXPLICIT TO PREVENT ISSUES ARISING FROM NON-DECLARED VARIABLES.
ADDING TO CODE WON'T AFFECT EXISTING MODULES. TURN ON IN EDITOR OPTIONS AND ALWAYS START WITH IT

Option Compare Database

Private Sub Command26_Click()
  Dim curDatabase As DAO.Database DIM db AS DAO.DATABASE (SHORTER, LIKE YOUR RS)
  Dim rs As DAO.Recordset
  
  Set curDatabase = CurrentDb
  Set rs = CurrentDb.OpenRecordset("Select * from [tblSignin]  ")WHY DECLARE AND SET DB VARIABLE IF YOU'RE NOT USING IT?
     SET RS=DB.OPENRECORDSET(sql VARIABLE,OPTIONS)
          If Not rs.RecordCount = 0 Then
       rs.MoveLast WHY? YOU ARE NOT USING COUNT ANY MORE.
       rs.MoveFirst  WHY? YOU DO NOT LOOP THROUGH THE RS & WHERE RECORD GETS INSERTED DEPENDS ON THE RS TYPE. 
   BE SURE TO FOLLOW LINK FOR INFO.  http://allenbrowne.com/ser-29.html
     End If
  ' MsgBox rs.RecordCount
     rs.AddNew
  DON'T SEE WHY YOU OPEN A FILTERED RECORDSET IF YOU'RE JUST ADDING RECORDS TO A TABLE
  **MAYBE JUST OPEN AN APPEND QUERY?
  rs.Fields(1) = Now()
  rs.Fields(2) = Form_frmSignin.txtUser INCORRECT FORM REFERENCE. 
  IF THIS FORM, ME.TXTUSER, OTHERWISE "FORMS!FRMSIGNIN.TXTUSER", WHICH MUST BE OPEN (NOT NECESSARILY VISIBLE)
       YOU REALIZE RS FIELDS ARE ZERO BASED? 2ND FIELD IS 1?
      rs.Fields(3) = Now()
       rs.Fields(4) = Now()
       rs.Fields(8) = Now()
      rs.Fields(9) = "AutoSave" 

    'If number of records is even enter "IN" , if odd enter "OUT"
      If rs.RecordCount Mod 2 = 1 Then
         rs.Fields(6) = "IN"
        Else: 
   rs.Fields(6) = "OUT"
      End If
            
     rs.Update
     
    Set curDatabase = Nothing SET DB = NOTHING
CLOSE THE RS FIRST
RS.CLOSE
    Set rs = Nothing
    MsgBox "The time sheet has been submitted.", _
           vbOKOnly Or vbInformation, "Kolo Bank"

           Form_frmSignin.Visible = False ASSUMING SOMETHING ELSEWHERE BRINGS THIS BACK
End Sub

**I admit the syntax for opening an append query and passing form fields without having to resort to DAO parameters and query defs escaped me for a long time. My numerous searches never uncovered a solution, but I did figure out a simple version on my own which you should be able to use. To insert a form field amount into a table using DoCmd.OpenQuery goes like this:
INSERT INTO test ( amount ) SELECT [forms]![form2].[OTHHOSP] AS amount;
To expand it, I think it would be like so:
INSERT INTO test ( amount,qty,dteDate ) SELECT [forms]![form2].[OTHHOSP] AS amount, [forms]![form2].txtQty AS qty, Date AS dteDate;

[George]

Thank you very much; This would take a while going through every bit of detail. Will get back to you later when I get it working.

[ssanfu]

I just noticed an error in the code

    'If number of records is even enter "IN" , if odd enter "OUT"
      If rs.RecordCount Mod 2 = 1 Then  
         rs.Fields(6) = "IN"
      Else: 
         rs.Fields(6) = "OUT"
      End If

1) Shouldn't have the colon after "Else". It has been depreciated - was used in the original BASIC.

2) "If rs.RecordCount Mod 2 = 1" , remainder of 1 means the record count is an odd number.

4 MOD 2 = 0 -> EVEN
5 MOD 2 = 1 -> ODD

Code should be

    'If number of records is even enter "IN" , if odd enter "OUT"
      If rs.RecordCount Mod 2 = 0 Then  
         rs.Fields(6) = "IN"
      Else 
         rs.Fields(6) = "OUT"
      End If