Update field in table

[Burden]

Can anyone help?

I have a form that i am designing which is used via a touchscreen. I
have created a box which has the active status on. When you press that
a pop up window appears and you choose the appropriate status. This
the gets put on the main form in the active status field and is saved
in to the active status field in the table.

I have 10 fields where i can store status updates and would like to
store the same information that i just selected in to a free field.
These fields are called status1 ~ status10

Example:
Status change on 01/01/08
Active status reads IH
I would like status1 to be IH if the field is empty. If it is not then
i want the IH to be put in status2 and so forth.

I also have a date field that is tied in with the status change but i
can look at that once i have cracked this part.

I have tried
If [TABLES]![ORDER STATUS TABLE]![Status3] = Null Then
[TABLES]![ORDER STATUS TABLE]![Status3].Value = [Forms]![FORM TOUCH
FIND]![ACTIVE STATUS].Value
End If

And it doesn't work. I think it is not tying to the record that i am
on. Does any one have any other code?

Any ideas.

 

[strive4peace]

Hi Burden,

rather than creating several fields to store status in your table, it
would be better to make a related table something like this:

OrderStatus
- OrdStatID, autonumber
- OrderID, long integer -- FK to Orders table
- StatusID, long integer -- FK to Status Codes table
- StatusDate, date

WHERE
you have an Orders table with primary key OrderID
you have a table for Status Codes something like this:

Statuses
- StatusID, autonumber
- Status, text
- StatusDescr, text (description of status code)

The current status for an Order will be the record in OrderStatus with
the MAX StatusDate


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Burden]

Hi Burden,

rather than creating several fields to store status in your table, it
would be better to make a related table something like this:

OrderStatus
- OrdStatID, autonumber
- OrderID, long integer -- FK to Orders table
- StatusID, long integer -- FK to Status Codes table
- StatusDate, date

WHERE
you have an Orders table with primary key OrderID
you have a table for Status Codes something like this:

Statuses
- StatusID, autonumber
- Status, text
- StatusDescr, text (description of status code)

The current status for an Order will be the record in OrderStatus with
the MAX StatusDate

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace

  *
    have an awesome day
  *

The problem being is that i have to link into an existing database and
have to work with the current table structure.

 

[Ken Sheridan]

In that case you will have to loop through the status controls on your main
form until one is Null then assign the value to that, e.g.

Dim frm As Form
Dim ctrl as Control
Dim n As Integer

Set frm = Forms!YourMainFormNameGoesHere

For n = 1 to 10
ctrl = frm.Controls("Status" & n)
If IsNull(ctrl) Then
ctrl = Forms.[FORM TOUCH FIND].[ACTIVE STATUS]
Exit For
End If
Next n

Ken Sheridan
Stafford, England

 

[Burden]

In that case you will have to loop through the status controls on your main
form until one is Null then assign the value to that, e.g.

Dim frm As Form
Dim ctrl as Control
Dim n As Integer

Set frm = Forms!YourMainFormNameGoesHere

For n = 1 to 10
    ctrl = frm.Controls("Status" & n)
    If IsNull(ctrl) Then
        ctrl = Forms.[FORM TOUCH FIND].[ACTIVE STATUS]
        Exit For
   End If
Next n

Ken Sheridan
Stafford, England

The problem being is that i have to link into an existing database and
have to work with the current table structure.

Thanks for that. I will give it a go and let you know the outcome.

 

[Burden]

In that case you will have to loop through the status controls on your main
form until one is Null then assign the value to that, e.g.

Dim frm As Form
Dim ctrl as Control
Dim n As Integer

Set frm = Forms!YourMainFormNameGoesHere

For n = 1 to 10
    ctrl = frm.Controls("Status" & n)
    If IsNull(ctrl) Then
        ctrl = Forms.[FORM TOUCH FIND].[ACTIVE STATUS]
        Exit For
   End If
Next n

Ken Sheridan
Stafford, England

The problem being is that i have to link into an existing database and
have to work with the current table structure.

- Show quoted text -

That does not seem to work at all. I get the error "Object variable or
with block variable not set"

I have tried the following and it kind of works but when i gets to a
field with something in it stops. So i don't beleive it is working the
way it should.

Say there is something in status1 & status3 but nothing in status2 and
status4 it will not do anything. But if there is nothing in status1 &
status2 it will work. It is as soon as it sees status3 containing data
it should put the new info in status 4 but it doesnt. It just stops.
Can anyone see why or either a way to inprove the command.

Private Sub Button_IH_Click()
On Error GoTo Err_Button_IH_Click

Dim status As String
status = "IH"
[Forms]![form - touch find]![Active status] = status

If IsNull(Me.Status1) Then [Forms]![form - touch find]![Status1] =
status: [Forms]![form - touch find]![Date1] = date: GoTo jump1:
If IsNull(Me.Status2) Then [Forms]![form - touch find]![Status2] =
status: [Forms]![form - touch find]![Date2] = date: GoTo jump1:
If IsNull(Me.Status3) Then [Forms]![form - touch find]![Status3] =
status: [Forms]![form - touch find]![Date3] = date: GoTo jump1:
If IsNull(Me.Status4) Then [Forms]![form - touch find]![Status4] =
status: [Forms]![form - touch find]![Date4] = date: GoTo jump1:
If IsNull(Me.Status5) Then [Forms]![form - touch find]![Status5] =
status: [Forms]![form - touch find]![Date5] = date: GoTo jump1:
If IsNull(Me.Status6) Then [Forms]![form - touch find]![Status6] =
status: [Forms]![form - touch find]![Date6] = date: GoTo jump1:
If IsNull(Me.Status7) Then [Forms]![form - touch find]![Status7] =
status: [Forms]![form - touch find]![Date7] = date: GoTo jump1:
If IsNull(Me.Status8) Then [Forms]![form - touch find]![Status8] =
status: [Forms]![form - touch find]![Date8] = date: GoTo jump1:
If IsNull(Me.Status9) Then [Forms]![form - touch find]![Status9] =
status: [Forms]![form - touch find]![Date9] = date: GoTo jump1:
If IsNull(Me.Status10) 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_IH_Click:
Exit Sub
Err_Button_IH_Click:
MsgBox Err.Description
Resume Exit_Button_IH_Click
End Sub

Thanks in advance.