Prev: rename csv file by removing date/time stamp before importing
Next: Tickbox Update
From: Irene_58 on 14 Apr 2010 04:50
That's a very big change at this stage, I've implemented the company's
timesheet system & project management reporting based on the tPNos table as
it stands.

I'm self-taught and whenever I need multiple fields to be a unique key - I
always add an auto-num instead. Clearly my bad, and I will go and delve into
this in my own time - promise.

Does this relate to getting the double increment? I don't see why setting up
a text field in the record affects the auto-num. Can you offer any insight
into why I seem to be accessing 2 different records when I do my AddNew?
--
Thanx, Irene


"John W. Vinson" wrote:

> On Tue, 13 Apr 2010 03:34:01 -0700, Irene_58
> <Irene58(a)discussions.microsoft.com> wrote:
>
> >I have a table (tPNos) containing Project info. The ProjectNo is a text
> >field because we have sub-projects with descriptive suffixes (e.g. 1400,
> >1400-01, 1400os). The first 4 digits are always numeric and equate to a
> >BasePno. I also have an auto-number PNumId field, that I use to link to
> >other tables.
>
> This whole exercise would be a lot easier if you had this field properly
> normalized. Fields should be "Atomic" - contain only one indivisible piece of
> information. Yours contains two - a BasePno and a suffix. Can you consider
> having a BasePno field and a Suffix field? That would let you have a table of
> BasePnos, which would be simple and numeric, related one to many to this
> table. You can always concatenate the BasePno field to the Suffix field to
> display the combination, and you can (and probably should) also define a
> unique two-field index on the combination.
> --
>
> John W. Vinson [MVP]
> .
>
From: BruceM via AccessMonster.com on 14 Apr 2010 07:46
You would probably do better just to use DMax to find the highest value, but
another point is that trying to use greater than or less than (> or <) in a
text field will likely cause problems. 100 and 10000 are both less than 7000
in a text sort order.

Here is DMax used to find the highest value in the number field Num1 in the
table Table1:

DMax("[Num1]","[Table1]")

As John Vinson mentioned, you would do better to have a related table where
suffixes are needed for sub-projects. For that matter, a sub-project table
may be called for. In any case, a unique index is set in table design view.
It can involve several fields, but it is not necessarily the primary key.
There is nothing wrong with using an autonumber for a one-field primary key.
Some people argue against using "artificial" keys, but most developers agree
that an autonumber can be a perfectly good choice for a primary key field.

Irene_58 wrote:
>Thanks to you both for your interest:
>
>txtProjectNo is the name of a textbox on the main form linked to the
>ProjectNo field.
>
>My function GetLastPno() opens the tPnos table, finds the highest
>BaseProject (ignoring everything > 7000 which relates to internal charge
>codes), closes tPnos and returns the BaseProject - see below. This is then
>incremented to give the first free project num and set into the textbox.
>
>I don't do anything to the auto-num field (PNoId) - it's not displayed on
>the form or explicitly referenced in my code. All that happens between the
>AddNew and the Save is the user filling in the rest of the form fields.
>
>For info:
>
>Public Function GetLastPno() As String
>On Error GoTo ErrHandle
> Dim strSQL As String
> Dim recPno As Recordset
>
> strSQL = "SELECT * FROM tPnos WHERE ([Project No] < '7000') ORDER BY
>[Project No] DESC"
> Set recPno = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
> GetLastPno = recPno![BaseProject]
> recPno.Close
>Exit Function
>ErrHandle:
> MsgBox Err.Description
>End Function
>
>> If the second chunk of code is just to force a save you can use:
>> Me.Dirty = False
>[quoted text clipped - 69 lines]
>> >MsgBox Err.Description
>> >End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1

From: Irene_58 on 14 Apr 2010 12:07
Hi,

I've resolved my problem - albeit empirically. I needed 2 changes:

1) I replaced the recordset .addnew / .update pair with RunCommand
acCmdRecordsGoToNew / acCmdSaveRecord

2) I set the focus to a textbox on the main form before setting any values
in the textboxes on the main form.

Neither change worked on its own. I'm guessing that the subform and the
main form were both trying to create a new record each causing the auto-num
to increment, and the save from the main form won?

Thanks for the comments - I'm off to invest some time in looking at multiple
keys and indexing.
--
Irene


"Irene_58" wrote:

> That's a very big change at this stage, I've implemented the company's
> timesheet system & project management reporting based on the tPNos table as
> it stands.
>
> I'm self-taught and whenever I need multiple fields to be a unique key - I
> always add an auto-num instead. Clearly my bad, and I will go and delve into
> this in my own time - promise.
>
> Does this relate to getting the double increment? I don't see why setting up
> a text field in the record affects the auto-num. Can you offer any insight
> into why I seem to be accessing 2 different records when I do my AddNew?
> --
> Thanx, Irene
>
>
> "John W. Vinson" wrote:
>
> > On Tue, 13 Apr 2010 03:34:01 -0700, Irene_58
> > <Irene58(a)discussions.microsoft.com> wrote:
> >
> > >I have a table (tPNos) containing Project info. The ProjectNo is a text
> > >field because we have sub-projects with descriptive suffixes (e.g. 1400,
> > >1400-01, 1400os). The first 4 digits are always numeric and equate to a
> > >BasePno. I also have an auto-number PNumId field, that I use to link to
> > >other tables.
> >
> > This whole exercise would be a lot easier if you had this field properly
> > normalized. Fields should be "Atomic" - contain only one indivisible piece of
> > information. Yours contains two - a BasePno and a suffix. Can you consider
> > having a BasePno field and a Suffix field? That would let you have a table of
> > BasePnos, which would be simple and numeric, related one to many to this
> > table. You can always concatenate the BasePno field to the Suffix field to
> > display the combination, and you can (and probably should) also define a
> > unique two-field index on the combination.
> > --
> >
> > John W. Vinson [MVP]
> > .
> >
From: BruceM via AccessMonster.com on 14 Apr 2010 14:35
The problem with the incrementing is that it cannot work with the field
values you have described and a text field. In a text sort, 100 and 10000
are less than 7000, and 77 is greater. For values with suffixes you will
probably get an error, but certainly nothing meaningful, from something like
1400-01 + 1. The main form "winning" does not enter into it.

Irene_58 wrote:
>Hi,
>
>I've resolved my problem - albeit empirically. I needed 2 changes:
>
>1) I replaced the recordset .addnew / .update pair with RunCommand
>acCmdRecordsGoToNew / acCmdSaveRecord
>
>2) I set the focus to a textbox on the main form before setting any values
>in the textboxes on the main form.
>
>Neither change worked on its own. I'm guessing that the subform and the
>main form were both trying to create a new record each causing the auto-num
>to increment, and the save from the main form won?
>
>Thanks for the comments - I'm off to invest some time in looking at multiple
>keys and indexing.
>> That's a very big change at this stage, I've implemented the company's
>> timesheet system & project management reporting based on the tPNos table as
>[quoted text clipped - 22 lines]
>> > display the combination, and you can (and probably should) also define a
>> > unique two-field index on the combination.

--
Message posted via http://www.accessmonster.com

From: Irene_58 on 15 Apr 2010 05:16
No. I haven't explained well enough, and you've mixed up the 2 fields being
incremented.

The text field I manually increment works perfectly well (agreed only
because our project numbers start at 1000 and currently run to 15nn; also the
base project is always 4 digits so the text / numeric conversions aren't a
problem). I am definitely going to follow your advice and change the
BaseProject to a number, to future-proof the application and add data-entry
checking to enforce the numeric requirements on the first 4 characters.

BUT, it was the auto-num field generated by Access when a record is added
that was my problem. I just want to be clear in case anyone else runs up
against this.
--
Irene
:-)

"BruceM via AccessMonster.com" wrote:

> The problem with the incrementing is that it cannot work with the field
> values you have described and a text field. In a text sort, 100 and 10000
> are less than 7000, and 77 is greater. For values with suffixes you will
> probably get an error, but certainly nothing meaningful, from something like
> 1400-01 + 1. The main form "winning" does not enter into it.
>
> Irene_58 wrote:
> >Hi,
> >
> >I've resolved my problem - albeit empirically. I needed 2 changes:
> >
> >1) I replaced the recordset .addnew / .update pair with RunCommand
> >acCmdRecordsGoToNew / acCmdSaveRecord
> >
> >2) I set the focus to a textbox on the main form before setting any values
> >in the textboxes on the main form.
> >
> >Neither change worked on its own. I'm guessing that the subform and the
> >main form were both trying to create a new record each causing the auto-num
> >to increment, and the save from the main form won?
> >
> >Thanks for the comments - I'm off to invest some time in looking at multiple
> >keys and indexing.
> >> That's a very big change at this stage, I've implemented the company's
> >> timesheet system & project management reporting based on the tPNos table as
> >[quoted text clipped - 22 lines]
> >> > display the combination, and you can (and probably should) also define a
> >> > unique two-field index on the combination.
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: rename csv file by removing date/time stamp before importing
Next: Tickbox Update