Changing three fileds with one action

Interesting places

news.only-4-geeks.com Forum Index » Access


	Changing three fileds with one action

Jump to:

Burden

Posted: Mon Sep 01, 2008 8:19 am Post subject: Changing three fileds with one action

hi there,
i was wondering if any one can spot what i am doing wrong here.

I have a table which has quite a lot of info in. Some of it being
status changes. These status's range from 1 - 10 and have date fields
next to them. I also have a active status field.

When i click on the button (code below) it changes the active status
field on my form not a problem. But it will not put it in the status1
or the date in the date box.

This button is on another form and when pressed it does its action and
closes. I want it to put the status change (below to be "AL
(Unpicked)") in the active status field and the next available status
field. and put the date that the change took place.

Private Sub Button_AL_Click()
On Error GoTo Err_Button_AL_Click
Dim Status As String

Status = "AL (Unpicked)"
[Forms]![form - touch find]![Active status] = Status
GoTo Statuschange:

Statuschange:
If (Me.Status1) = Null Then [Forms]![form - touch find]![Status1] =
Status: [Forms]![form - touch find]![Date1] = Date: GoTo jump1:
If (Me.Status2) = Null Then [Forms]![form - touch find]![Status2] =
Status: [Forms]![form - touch find]![Date2] = Date: GoTo jump1:
If (Me.Status3) = Null Then [Forms]![form - touch find]![Status3] =
Status: [Forms]![form - touch find]![Date3] = Date: GoTo jump1:
If (Me.Status4) = Null Then [Forms]![form - touch find]![Status4] =
Status: [Forms]![form - touch find]![Date4] = Date: GoTo jump1:
If (Me.Status5) = Null Then [Forms]![form - touch find]![Status5] =
Status: [Forms]![form - touch find]![Date5] = Date: GoTo jump1:
If (Me.Status6) = Null Then [Forms]![form - touch find]![Status6] =
Status: [Forms]![form - touch find]![Date6] = Date: GoTo jump1:
If (Me.Status7) = Null Then [Forms]![form - touch find]![Status7] =
Status: [Forms]![form - touch find]![Date7] = Date: GoTo jump1:
If (Me.Status8) = Null Then [Forms]![form - touch find]![Status8] =
Status: [Forms]![form - touch find]![Date8] = Date: GoTo jump1:
If (Me.Status9) = Null Then [Forms]![form - touch find]![Status9] =
Status: [Forms]![form - touch find]![Date9] = Date: GoTo jump1:
If (Me.Status10) = Null Then [Forms]![form - touch find]![Status10] =
Status: [Forms]![form - touch find]![Date10] = Date
jump1:
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
[Forms]![form - touch find].SetFocus
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.RunCommand acCmdSaveRecord

Exit_Button_AL_Click:
Exit Sub
Err_Button_AL_Click:
MsgBox Err.Description
Resume Exit_Button_AL_Click
End Sub

The status fields are not on the form, only in the table, is the
problem?

Burden

Burden

Posted: Mon Sep 01, 2008 8:54 am Post subject: Re: Changing three fileds with one action

Ok thanks for looking but i have sorted it buy rearranging the code to
the following.

Private Sub Button_AL_Click()
On Error GoTo Err_Button_AL_Click

Dim Status As String
Status = "AL (Unpicked)"

If IsNull(Me.Status1) Then Me.Status1 = Status: Me.Date1 = Date: Me.
[Active status] = Status: GoTo jump1:
If IsNull(Me.Status2) Then Me.Status2 = Status: Me.Date2 = Date: Me.
[Active status] = Status: GoTo jump1:
If IsNull(Me.Status3) Then Me.Status3 = Status: Me.Date3 = Date: Me.
[Active status] = Status: GoTo jump1:
If IsNull(Me.Status4) Then Me.Status4 = Status: Me.Date4 = Date: Me.
[Active status] = Status: GoTo jump1:
If IsNull(Me.Status5) Then Me.Status5 = Status: Me.Date5 = Date: Me.
[Active status] = Status: GoTo jump1:
If IsNull(Me.Status6) Then Me.Status6 = Status: Me.Date6 = Date: Me.
[Active status] = Status: GoTo jump1:
If IsNull(Me.Status7) Then Me.Status7 = Status: Me.Date7 = Date: Me.
[Active status] = Status: GoTo jump1:
If IsNull(Me.Status8) Then Me.Status8 = Status: Me.Date8 = Date: Me.
[Active status] = Status: GoTo jump1:
If IsNull(Me.Status9) Then Me.Status9 = Status: Me.Date9 = Date: Me.
[Active status] = Status: GoTo jump1:
If IsNull(Me.Status10) Then Me.Status10 = Status: Me.Date10 = Date: Me.
[Active status] = Status

jump1:
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

Exit_Button_AL_Click:
Exit Sub
Err_Button_AL_Click:
MsgBox Err.Description
Resume Exit_Button_AL_Click
End Sub

Burden

Posted: Mon Sep 01, 2008 9:02 am Post subject: Re: Changing three fileds with one action

Well i thought i had... :/

It now seems that everytime i am changing a record. It is changing the
data on the first record rather than the record that i am on. i.e. if
i change record 12345 it is adding the status changes to record 1.

How can i make it change the record that i am on?

John W. Vinson

Posted: Mon Sep 01, 2008 6:01 pm Post subject: Re: Changing three fileds with one action

On Mon, 1 Sep 2008 01:19:27 -0700 (PDT), Burden <james.burden@sonic-comms.com>
wrote:

Quote:

STOP!

You're having trouble because your table structure *IS WRONG*.

If you have a one (something) to many (statuses and dates) relationship, you
need a separate status table. It would have fields for a foreign key link to
your current table, a status, and a status date, and perhaps a number field to
indicate status 1, 2, 3 etc.

You'll find your current "spreadsheet" design will cause you constant trouble.
You're using a relational database - use it relationally!

--

John W. Vinson [MVP]

Page 1 of 1 .:.



Awesome Website Templates ©

Ciekawe artykuÅ‚y gry do pobrania bet365 artykuÅ‚y biurowe ogrod

Changing three fileds with one action

Awesome Website Templates ©