Changing three fileds with one action

B

Burden

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
 
B

Burden

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
 
B

Burden

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?
 
J

John W. Vinson

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
Click to expand...

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update field in table 5
Checking for data with the same number in another table 5
Invalid use of Null 15
access97 to 2002 search button conversion error 1
If . . . Then On Current Event 7
If date field is 1 or not entered then prompt to choose before clo 1
VBA in On Click Event Mystery 1
Problems with Expression Builder and Proprty Sheets 2

Top