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: 785
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,623
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,755
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    13th December 2018 - 10:23 AM