How to refer to a linked field in the record source table of the Form ?

[gykiang]

Please pardon me for being a novice of Access. I trying to create a "Packing List" using Access form. The Form is very huge & clumps that I'm quite sure that it will ultimate corrupt the Form if I keep on adding fields to the form or in the record source which contain several tens of linked tables.

Below is what I have written in the event procedure of the Packing List Form :
Private Sub Item3_AfterUpdate()
If Me!Item3 = "2." Then
Me.PackDesc3 = Me.Desc1

ElseIf Me!Item3 = "3." Then


Me.PackDesc3 = Me.Desc2

ElseIf Me!Item3 = "4." Then
Me.PackDesc3 = Me.Desc3

ElseIf IsNull(Me!Item3) Then
Me.PackDesc3 = " "

End If

End Sub

- The code of [Item3] above is an example as the form is having from [Item0] to [Item60]. All these Item fields are bounded field to other forms/tables such as Invoice etc (so you can understand how huge the form can be subsequently !!!)

- [PackDesc3] was created in the form, but they were supposed to be bounded (I have yet to add or create new table with 61 of the [PackDesc] fields to one of the record source Tables).

- [Desc3] are from [Desc] fields of the Invoice Table (joined in the record source) in the Packing List form. They are description of the product.

The [PackDesc] fields are supposed to be blank if related [Item] fields are null. However, if [Item] fields equal to any of the same Item No. in the Invoice Table, then [PackDesc] = [Desc].

In the above code of (Me!Item3) example, there is no problem if I add all the 61 [Desc] fields to the record source of the Packing List Form & again create invisible textfields of all the [Desc] in order for them to show up in [PackDesc] fields when the criteria of [Item] fields when same number of Packing List Form & Invoice Table is met.

My question : Is there anyway I can short cut the script by :

1) having unbound fields for [PackDesc0] to [PackDesc60] but each row of records will change according to the input of [Item] fields.

2) Without having to add [Desc] fields in the record source of Packing List Form & create multiple invisible textfields of [Desc] to make their referral to [PackDesc] fields.

Hope what I explained above is understandable to the experts out there.

[amrut]

It seems that your data is not normalized.

I have yet to add or create new table with 61 of the [PackDesc] fields to one of the record source Tables

Are you talking about 61 records or 61 fields ??
Why not a table for [PackDesc] ? That table can have the destination field [Desc] in the same table if there is only one [Desc] for each [PackDesc]. If there are more destinations for a [PackDesc], you will need a junction table.
Can you post a screenshot of your table relationships ?

[gykiang]

Yes, it is 61 fields (not records).

Below is the design view of the core part of the form.


We are distributing chemical company with very wide variety. In the form design,

|Item|Quantity|Description| are common records links to Invoice Table with primary common key of "SalesID" in Query Table "1-SALES DATA Query".

The problem in designing this form is that the items in one invoice can run up to 25 items (in Invoice Form, it is manageable in 2 pages), but not in Packing List. As you can see from the form design, per item of

|Batch No.|Manufacturing Date|Expiry Date|Parcel|Netweight|Remarks|

is unknown, and per item have as many as 10 batches with same number of associated fields to each batch. Therefore if I have 25 items, with provision of maximum 10 batches per item, then we will have 250 fields per batch detail (eg. Batch No.). This is why I created [PackDesc] to mirror the records from Invoice Table to avoid repetition of too many fields.

Below is what I have modified from my previous code, but sometime it works and sometime it doesn't, especially for the first [Item] (= "1."). Any clue of what I have done wrong ?

Private Sub Item3_AfterUpdate()
'* SAVE CURRENT RECORD
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "1-SALES DATA Query"

stLinkCriteria = "[SalesID]=" & Me![SalesID]

If Me!Item3 = "2." Then
Me.PackDesc3 = [Quantity1] & " " & [CMB-Product_1.SalesUM] & " of " & [CMB-Product_1.Description]

ElseIf Me!Item3 = "3." Then
Me.PackDesc3 = [Quantity2] & " " & [CMB-Product_2.SalesUM] & " of " & [CMB-Product_2.Description]

ElseIf Me!Item3 = "4." Then
Me.PackDesc3 = [Quantity3] & " " & [CMB-Product_3.SalesUM] & " of " & [CMB-Product_3.Description]

ElseIf IsNull(Me!Item3) Then
Me.PackDesc3 = " "

End If

End Sub

[amrut]

Can you post a screenshot of your table relationships ? Are you using an unbound form ?

[gykiang]

Here's the screenshot of the table relationship. It's kind of messy...

[amrut]

Here's the screenshot of the table relationship. It's kind of messy...

It looks messy because your database is not normalised. There are many threads in this forum and http://rogersaccessblog.blogspot.com...omplex-pc.html. This is also a good article http://office.microsoft.com/en-001/a...010120534.aspx

For ex. the table 1-SALES PAGE2, it seems there are many similar fields Product1, Product2 etc. If they are of similar type, there should be a Product Table with a foreign key in the related Sales table.