Steve Turner
sturner at mseco.com
Thu Aug 26 15:58:27 CDT 2010
Hi Stuart,
The NewJobNo table is in Access mdb
We link in all the SQL tables with ODBC. I will probably change the
report query to an expression that I have to key in at time of
generation. I can see on the properties tab for the text box that there
is also no plain text selection. Strange though that if you set it to
Fixed and 2 decimal places it won't write that to the file as text
without drooping the last zero's. If I don't put the period in there it
will write it.
Oh just tried the Job = txtNewJobNo.value you suggested. didn't work
either.
Steve A. Turner
Controller
Mid-South Engineering Co. Inc
E-Mail: sturner at mseco.com and saturner at mseco.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Thursday, August 26, 2010 3:43 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Droping trailing zero
"his is all tied to a SQL express table with all the jobs in them."
Is the table NewJobNo in your Access mdb or in SQL Server?
If in SQL Server;
How are you connecting to the data? ODBC?
"text" is not a SQL Server data type. Is the text field
char(x),varchar(x), nchar(x),
nvarchar(x)?
Why are you storing the number in what appears to be a single row table?
Is it just to have it
available for the recordseet underlying the report? If so there are a
number of alternatives to
storing it in a table including:
1. A static function
2. Referencing the form's control directly in the report
3. A global variable.
--
Stuart
On 26 Aug 2010 at 15:15, Steve Turner wrote:
> Lambert,
> The Table NewJobNo has one field LongJobNum and it is a text field.
> I
> put the text box on a form with other controls and named it txtNewJob
> so I could key in the number on the form and hit a print button and it
> would write it to the Table NewJobNo as LongJobNum. The report reads
> this table for the LongJobNum to print the sheet I need. I was trying
> to not have to open the table, type in the job number, go to the
> reports section and find the report and call it up to print. These job
> numbers can start with an alpha char and still have .00 such as
> M501.00 as the number. This is all tied to a SQL express table with
> all the jobs in them. We have a custom timesheet program for our time
> that writes to SQL tables and we use Access to do all the reports. I
> was waiting for our programmer to see if he couldn't link to the
> report in access from the timesheet program to do this but don' know
> when we will have him back. In the mean time I was trying to simplify
> how I now get these sheets printed. The timesheet is written in VB6.
> I've set the property of the text box to Fixed, Standard, general all
> to no avail. Tried Auto, 2 decimal places etc.
>
> Steve A. Turner
> Controller
> Mid-South Engineering Co. Inc
> E-Mail: sturner at mseco.com and saturner at mseco.com
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan,
> Lambert Sent: Thursday, August 26, 2010 2:59 PM To: Access Developers
> discussion and problem solving Subject: Re: [AccessD] Droping trailing
> zero
>
> Steve,
>
> Are you sure about the data type of the table field? I just ran this
> code...
>
> Dim dbs As DAO.Database
> Dim rstNewJobNo As DAO.Recordset
> Dim Job As String
>
> Set dbs = CurrentDb
> Job = txtNewJobNo
> Set rstNewJobNo = dbs.OpenRecordset("NewJobNo")
> With rstNewJobNo
> .AddNew
> !txtJobNum = Job
> !LongJobNo = Job
> !DoubleJobNo = Job
> .Update
> .Close
> End With
> Set rstNewJobNo = Nothing
> DoCmd.OpenTable "NewJobNo"
>
> (Note your code uses .Edit with no find method, so it gives a "no
> current record" error )
>
> The table I am using has three fields: txtJobNum defined as Text,
> LongJobNo which is a Long and DoubleJobNo which is a Double.
>
> When I run the code I get the following results in the table...
>
> txtJobNum : 10.40
> LongJobNo : 10
> DoubleJobNo : 10.4
>
> So it looks like your filed might be a double or single FP value.
>
> Lambert
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve
> Turner Sent: Thursday, August 26, 2010 3:40 PM To: Access Developers
> discussion and problem solving Subject: Re: [AccessD] Droping trailing
> zero
>
> Lambert, here is what I pasted in to make a report print button for
> new jobs. The txtNewJob is the textbox I set up to get the number on
> the form. If I use a number not ending in zero it works.
>
> Private Sub btnPrNewJob_Click()
>
> Dim rstNewJobNo As Recordset
> Dim strError As String
> Dim dbs As Database
> Dim qdf As QueryDef
> Dim rst As Recordset
> Dim db As Database
> Dim Job As String
>
> Set dbs = CurrentDb
> Job = txtNewJob
> Set rstNewJobNo = dbs.OpenRecordset("NewJobNo")
> With rstNewJobNo
> .Edit
> !LongJobNum = Job
> .Update
> End With
>
> Dim strDocName As String
> strDocName = "rptProjectNumberAssignmentSheet"
> On Error GoTo Err_btnPrNewJob_Click
>
> DoCmd.OpenReport strDocName, acViewPreview
> Exit_btnPrNewJob_Click:
> Exit Sub
>
> Err_btnPrNewJob_Click:
> If Err = ConErrRptCanceled Then
> Resume Exit_btnPrNewJob_Click
> Else
> MsgBox Err.Description
> Resume Exit_btnPrNewJob_Click
> End If
> End Sub
>
> Steve A. Turner
> Controller
> Mid-South Engineering Co. Inc
> E-Mail: sturner at mseco.com and saturner at mseco.com
>
> <snip>
> --
> 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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com