asked on

Access 2003 subform does not update text field

Hi all.
I have an Access 2003 form where the OrderID is in the main form and I have a subform that contains all the items for the work order, the subform is in "Continuous Form" view. They are linked by the OrderID.

All the fields are bounded directly from the table, except one "txtPrintDescription" this one is not bound from the table. In the code below, we create the text that will appear in the txtPrintDescription textbox. The problem I'm having is if there are 3 items for the work order then all 3 txtPrintDescription gets updated with the last of the 3.

But if I added a msgbox after the code "Me.mySubform!txtPrintDescription = descr" at the end to see what descr is, it shows the correct description. Something is happening once the form refreshes I think?

Example:

Table:

OrderID: A111
Item1: 12--RedHat
Item2: 34--BlueHat
Item3: 56--YellowHat

Subform shows:
Item1: YellowHat
Item2: YellowHat
Item3: YellowHat

What am I doing wrong? Thank you in advance.

Me.mysubform!txtOrderID = Me.OrderIDField

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim descr As String
Dim TheOrderID As String

TheOrderID = Me.OrderIDField



Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdText
    .CommandText = "SELECT * FROM myTableA WHERE WorkOrderID = " & TheOrderID & "  AND WorkOrderItems.ItemType=1"
    .Execute
End With
    
    rs.Open cmd, , adOpenDynamic, adLockOptimistic


If Not (rs.EOF Or rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True

    
    If Not IsNull(rs!Quantity) And Not IsNull(rs!CartonSize) And rs!CartonSize <> 0 Then
   
        descr = descr & " " & Round(rs!Quantity / rs!CartonSize, 0) & " boxes"
    End If

Me.mySubform!txtPrintDescription = descr
 
    rs.MoveNext

Loop

End If
rs.Close
Set rs = Nothing

Open in new window

Jeffrey Coachman

Not sure I understand the full scope here...
(perhaps another expert can see what is happening here...?)

Is all that recordset code just used to populate this unbound control?

Why not add this "PrintDescription" to the underlying source for the form?

Or better yet, post a sample of this database so we can perhaps have some context here...

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
(Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman

kmslogic

You want to be calculating that value for each record (btw the calculation will never yield the example data you gave since it seems to have the word boxes in it).

So, instead of making the textbox unbound, maybe bind it to a public function (which you'd put in a module) and in that function calculate your description for each row.

The binding on the textbox would look like

=MyFunction([OrderIDField], [ItemType])

and the function would look like

public MyFunction(byval strOrderType as string, lngItemType as Long) as string
      (do your magic to find descr string -- use strOrderType and lngItemType in your query to find the values)

      MyFunction = descr
end function

Open in new window

ASKER CERTIFIED SOLUTION

Hamed Nasr

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

printmedia

ASKER

Tweaked your code a bit and got it to work. Thanks!