UtterAccess.com
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    
 
   
techfixes
post Nov 21 2018, 01:19 AM
Post#1



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:




CODE
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
 
isladogs
post Nov 21 2018, 03:33 AM
Post#2



Posts: 796
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
 
cheekybuddha
post Nov 21 2018, 04:41 AM
Post#3


UtterAccess VIP
Posts: 10,650
Joined: 6-December 03
From: Telegraph Hill


Hi,

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


hth,

d

--------------------


Regards,

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


UtterAccess VIP
Posts: 7,758
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):

Forms!MainFormName!SubformControlName.Form!ControlName

For a sub-subform the logic is the same:

Forms!MainFormName!SubformControlName1.Form!SubformControlName2!ControlName

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

Me.SubformControlName.Form.ControlName

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

CODE
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    18th December 2018 - 06:42 AM