[AccessD] Format Field

On 13 Oct 2003 at 6:32, Hollis,Virginia wrote:

> No, I named the field EFRNo in the search form (where I would double-click).
> 
I presume you mean you named the textbox?

> I do not want to use the Format of the field because when you click in the
> field the formatting disappears. Instead of EF-00011, when you click in the
> field it shows 11. I want it to show the complete number with the EF-.
> However, when I do use the Format of the field it works, the form displaying
> all the data does open.
> 
That's because you are now passing the actual report number to the 
strLinkCriteria.

> I am not sure what I did now... when I double click the failure number on
> the search form, I get a box with EF & wanting me to enter a parameter & the
> form opens blank.
>
> I even tried adding the format to the query for the search form and using
> that as the control source. EFRNo:
> Format([tblSystemMain].[FailureReportNo],"""EF-""00000")
> 
> What am I missing?
> 

If the textbox contains "EF-00011", and you want to find the record 
with the Report Number 11, you need to convert the formatted data  
back to it's numeric equivalent. Make your criteria

  stLinkCriteria = "[tblSystemMain].[FailureReportNo]=" &
  Val(Mid$(EFRNo],4))


> Virginia
> 
> 
> 
> -----Original Message-----
> From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg]
> Sent: Saturday, October 11, 2003 5:43 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Format Field
> 
> 
> On 11 Oct 2003 at 10:33, Hollis,Virginia wrote:
> 
> > I am having problems with a formatted field. The FailureReportNo is a
> number
> > field.
> >  
> > On a search form, I want to double-click on the failure report number and
> > open another form that contains all the failure data.
> >  
> > On the search form I have the FailureReportNo with the Control Source:
> > =Format([FailureReportNo],"""EF-""00000")
> >  
> > On the double-click of the FailureReportNo I have:
> >  
> >     stLinkCriteria = "[tblSystemMain].[FailureReportNo]=" &
> > Me![FailureReportNo]
> >     DoCmd.OpenForm stDocName, , , stLinkCriteria
> >     DoCmd.Close acForm, "frmFind"
> >  
> > The problem is the search form FailureReportNo field contains #Error & if
> I
> > double click on the field (to test it anyway) I get Type Mismatch. Do I
> have
> > my quotes wrong?
> >  
> 
> My guess:
> You created the textbox by dragggin it from the FieldView window andf 
> then change dthe control source afterwards. 
> 
> As a result, you have the textbox named "FailureReportNo" and are 
> using the field "FailureReportNo" in a function as the control source 
> (causing a circular reference)
> 
> When you double click, it use the textbox name 
> (Me![FailuyreReportNo]) to build the string stLinkCriteria which 
> becomes "[tblSystemMain].[FailureReportNo]=EF-1234"
> 
> Either - rename the textbox to txtFailureReportNo
> or - reset the control source to  a simple "FailureReportNo" and 
> format it using the format property of the textbox.
> 
> 
>  
> -- 
> Lexacorp Ltd
> http://www.lexacorp.com.pg
> Information Technology Consultancy, Software Development,System 
> Support.
> 
> 
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.