X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Dao Recordset - Code Working Elsewhere, Access 2016    
post Nov 21 2018, 01:19 AM

Posts: 72
Joined: 9-November 16

I have exactly duplicated my working code from elsewhere in the program.

However when I try to change the code to read from a Textbox I get the error:

Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryInvoiceDisplay")

    rst.Filter = "[CUSTCODE] =" & [Forms]![sfrmEMAILMULTI].OpenArgs                        ' *** WORKING
    rst.Filter = "[CUSTCODE] =" & [Forms]![sfrmEMAILMULTI]![txt_EMAILMULTI1]            ' *** NOT WORKING

    Set rstFiltered = rst.OpenRecordset

Can anyone tell me why this is so?
This post has been edited by techfixes: Nov 21 2018, 01:24 AM

- Techfixes

I am the Pig Man
Go to the top of the page
post Nov 21 2018, 03:33 AM

UtterAccess VIP
Posts: 1,344
Joined: 4-June 18
From: Somerset, UK

Is txtemailmulti for a text field? If so use text delimiters.

BTW you posted this twice. Can you edit the other thread so people only respond on this one.

Go to the top of the page
post Nov 21 2018, 04:41 AM

UtterAccess VIP
Posts: 11,156
Joined: 6-December 03
From: Telegraph Hill


rst.Filter = "[CUSTCODE] =" & Forms.sfrmEMAILMULTI.Form.txt_EMAILMULTI1





David Marten
Go to the top of the page
post Nov 21 2018, 08:34 AM

UtterAccess VIP
Posts: 7,878
Joined: 24-May 10
From: Downeast Maine

I'm not sure why the first one worked. I would have guessed otherwise.

To provide a little more detail, a reference to a subform needs to reference the parent form, then the Form property of the subform control (the "box" that contains the subform):


For a sub-subform the logic is the same:


If the code is in the code module for the parent form it can be simplified a little:


It seems to me you could add the filter as a Where condition for the recordset SQL. It may simplify things a little:

Dim strSQL As String
Dim db     As DAO.Database
Dim rst    As DAO.Recordset

Set db = CurrentDb
strSQL = "SELECT * FROM qryInvoiceDisplay WHERE [CUSTCODE] = " & [Forms]!ParentFormName![sfrmEMAILMULTI].Form![txt_EMAILMULTI1]
Set rst = CurrentDb.OpenRecordset(strSQL)

If CUSTCODE is a text value it needs delimiters:
strSQL = "SELECT * FROM qryInvoiceDisplay WHERE [CUSTCODE] = '" & [Forms]!ParentFormName![sfrmEMAILMULTI].Form![txt_EMAILMULTI1] & "'"

If the code is in the parent form's code module. The Me prefix means that what follows is a property of the form with which the code module is associated. Controls and fields are properties for this purpose.
strSQL = "SELECT * FROM qryInvoiceDisplay WHERE [CUSTCODE] = '" & Me.[sfrmEMAILMULTI].Form.[txt_EMAILMULTI1] & "'"
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th May 2019 - 07:54 PM