Adding data in continuous subform

[bdp via AccessMonster.com]

I'm "learning by doing", so it's likely that I've done something obviously
wrong but can't see it - so here's a cry for help. Situation: Form (invoice)
and subform (line items), the first in form view, the second as continuous
forms, each bound to its table (invoice: PK is invoice number, and invoice
lines, PK is invoice number and product number). Looks like Northwind's
order/order details tables.
As soon as I enter the product number in the first line of the subform (allow
additions, deletions, edits = true, dataentry = false), it shows the selected
product's details in that line (record selector = pencil, so not yet saved)
and repeats it in the "next" (i.e. new) record (asterisk in the record
selector). The only difference between the two is that the "asterisk" line
doesn't contain the product number that I'd entered - but the rest of the
data is repeated.
When I tab out of the last text box on the first record, a "LostFocus"
procedure saves the record I've just left and goes to the next record. On
the screen, the line I'd been entering now has a blank record selector, the
line originally carrying the asterisk now has a right-pointing arrow (it's
been saved), but still no product number (though the invoice lines table does
have that product number).
If I then click in the first record (the one I entered originally), its
record selector changes to the right-arrow symbol, and the second record
(still showing the copy of what I entered but without the product number)
gets its asterisk back. I click in that second record to change it to a new
product, the first one reverts to a pencil and the focus goes to the next
field in that first record.
In short, I can't get beyond entering one record, and I'm becoming
exceedingly frustrated ... so what am I doing wrong? It must be simple, I've
probably been looking at it for hours and not recognised it.
Brian.

 

[Allen Browne]

Brian, open your subform in design view.
Right-click the Product, and choose Properties.
On the Data tab, what do you have for Control Source?
It should be the name of the field where the product number is stored.
If it is unbound, it will be the same on every row of the continuous form
(including the new row.)

If that is not the issue, do you have any code in the events of this
control, or of the form? Anything that would assign a value or Default Value
to it?

(BTW, you don't need the Save in the LostFocus of the last control. Access
will save it automatically at that point.)

 

[bdp via AccessMonster.com]

Allen,

Thanks for some fast footwork. The Control Source is indeed the product
number field in the invoice lines table.
Yes, I do have some event code. Most of it has to do with completing
controls on the form that aren't derived from invoice lines (there are
inventory implications requiring stock levels and reorder parameters to be
available in the form, for example) Probably the most significant is on
BarcodeLost_Focus (the intention is to use a barcode scanner to identify the
product; a separate table associating barcode with product number retrieves
and inserts the product number in the form). The code there is:

Private Sub Barcode_LostFocus()
Dim strBarcode As String
Dim longSizeID As Long
Dim strSiteCode As String
Dim longProductID As Long
Dim boolFoundit As Boolean

mstrSiteCode = Parent!SiteCode
strBarcode = Me!Barcode
If Not (IsNull(strBarcode) Or IsEmpty(strBarcode) Or strBarcode = "" _
Or strBarcode = "?") Then
Set rst = New ADODB.Recordset
rst.Open "Select * from Barcodes where Barcode = """ & strBarcode &
"""", CurrentProject.Connection, adOpenStatic
longSizeID = rst!BarcodeSizeID
rst.Close
Set rst = Nothing
Else
boolFoundit = GetBasicData(longProductID, longSizeID, "S")
DoCmd.Close acForm, "FormFind_SelectProduct"
If Not boolFoundit Then
Exit Sub
End If
End If
Me!InvoiceLineSizeID = longSizeID
Me!SoldProductName = DLookup("[ProductName]", "[Products]", "[ProductID]
= " & DLookup("[ProductID]", "Sizes", "[SizeID] = " & longSizeID))
Me!SoldSizeType = DLookup("[SizeType]", "Sizes", "[SizeID] = " &
longSizeID)
msingleConversionFactor = DLookup("[SizeStockUOMConversion]", "Sizes", "
[SizeID] = " & longSizeID)
mstrAltUOM = DLookup("[SizeStockAltUOM]", "Sizes", "[SizeID] = " &
longSizeID)
If IsNull(mstrAltUOM) Or IsEmpty(mstrAltUOM) Or mstrAltUOM = "" Then
Me!SoldAltUOM.Enabled = False
Me!InvoiceLineQtySold.Enabled = False
Else
Me!SoldAltQty = Me!InvoiceLineQtySold * msingleConversionFactor
End If
If IsNull(DLookup("[StockShop]", "Stock", ("[StockSizeID] = " &
longSizeID & " AND [StockSiteCode] = """ & mstrSiteCode & """"))) Then
msingleTotalStock = 0
msingleStockAvailable = 0
Me!InvoiceLineUnitPrice = 0
Else
msingleTotalStock = DLookup("[StockShop]", "Stock", ("[StockSizeID] =
" & longSizeID & " AND [StockSiteCode] = """ & mstrSiteCode & """"))
msingleTotalStock = msingleTotalStock + DLookup("[StockBack]",
"Stock", ("[StockSizeID] = " & longSizeID & " AND [StockSiteCode] = """ &
mstrSiteCode & """"))
msingleStockAvailable = msingleTotalStock - DLookup("[StockAllocated]
", "Stock", ("[StockSizeID] = " & longSizeID & " AND [StockSiteCode] = """ &
mstrSiteCode & """"))
Me!InvoiceLineUnitPrice = DLookup("[StockSellPrice]", "Stock", ("
[StockSizeID] = " & longSizeID & " AND [StockSiteCode] = """ & mstrSiteCode &
""""))
End If
Me!SoldUOM = DLookup("[SizeStockUOM]", "Sizes", "[SizeID] = " &
longSizeID)
Me!SoldAltUOM = mstrAltUOM
Call Price_line
Me!Msg15_result = " "
Me!Msg16_result = " "
Me!InvoiceLineQtySold.SetFocus

End Sub

.... and didn't I have fun with those Dlookups and the number of double-quote
marks! It was another of your posts that put me right on that point, so
thanks for that guidance, too.
Some information about the calls:
GETBASICDATA is a sub that opens a separate form to allow the user to select
product and size (size being a subordinate to product -- think different
sizes of cans of beans, for example), and returns both product and size IDs.
PRICE_LINE merely calculates the extension unit price * quantity.
Variables prefixed with "m" are Dim-ed at module level.
Hope this isn't information overload!

Brian.

Brian, open your subform in design view.
Right-click the Product, and choose Properties.
On the Data tab, what do you have for Control Source?
It should be the name of the field where the product number is stored.
If it is unbound, it will be the same on every row of the continuous form
(including the new row.)

If that is not the issue, do you have any code in the events of this
control, or of the form? Anything that would assign a value or Default Value
to it?

(BTW, you don't need the Save in the LostFocus of the last control. Access
will save it automatically at that point.)

I'm "learning by doing", so it's likely that I've done something obviously
wrong but can't see it - so here's a cry for help. Situation: Form

[quoted text clipped - 34 lines]

probably been looking at it for hours and not recognised it.
Brian.

 

[bdp via AccessMonster.com]

Allen,

I didn't reply to your point about default values. Apologies - I was rushing
to do other things as well, so that one slipped. The tables define default
values for most fields (invoice number in the invoices table doesn't default,
but that doesn't seem to be related to this issue). It does default - to
zero - in the invoice lines table, but the master/child definition for the
subform control fills in the invoice number on the invoice lines from the
invoice number in the main form. The product number (Size ID) in the invoice
lines table has no default on it, nor in the properties sheet on the subform.

Thanks for the reminder about the redundant save - I'll apply scissors now.

Brian.

Brian, open your subform in design view.
Right-click the Product, and choose Properties.
On the Data tab, what do you have for Control Source?
It should be the name of the field where the product number is stored.
If it is unbound, it will be the same on every row of the continuous form
(including the new row.)

If that is not the issue, do you have any code in the events of this
control, or of the form? Anything that would assign a value or Default Value
to it?

(BTW, you don't need the Save in the LostFocus of the last control. Access
will save it automatically at that point.)

I'm "learning by doing", so it's likely that I've done something obviously
wrong but can't see it - so here's a cry for help. Situation: Form

[quoted text clipped - 34 lines]

probably been looking at it for hours and not recognised it.
Brian.

 

[Allen Browne]

Without going through your code in detail, why LostFous?

If there is no change, nothing would need updating?
If so, could you use the AfterUpdate of the text box, so it only fires if
something gets entered?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for some fast footwork. The Control Source is indeed the product
number field in the invoice lines table.
Yes, I do have some event code. Most of it has to do with completing
controls on the form that aren't derived from invoice lines (there are
inventory implications requiring stock levels and reorder parameters to be
available in the form, for example) Probably the most significant is on
BarcodeLost_Focus (the intention is to use a barcode scanner to identify
the
product; a separate table associating barcode with product number
retrieves
and inserts the product number in the form). The code there is:

Private Sub Barcode_LostFocus()
Dim strBarcode As String
Dim longSizeID As Long
Dim strSiteCode As String
Dim longProductID As Long
Dim boolFoundit As Boolean

mstrSiteCode = Parent!SiteCode
strBarcode = Me!Barcode
If Not (IsNull(strBarcode) Or IsEmpty(strBarcode) Or strBarcode = "" _
Or strBarcode = "?") Then
Set rst = New ADODB.Recordset
rst.Open "Select * from Barcodes where Barcode = """ & strBarcode &
"""", CurrentProject.Connection, adOpenStatic
longSizeID = rst!BarcodeSizeID
rst.Close
Set rst = Nothing
Else
boolFoundit = GetBasicData(longProductID, longSizeID, "S")
DoCmd.Close acForm, "FormFind_SelectProduct"
If Not boolFoundit Then
Exit Sub
End If
End If
Me!InvoiceLineSizeID = longSizeID
Me!SoldProductName = DLookup("[ProductName]", "[Products]",
"[ProductID]
= " & DLookup("[ProductID]", "Sizes", "[SizeID] = " & longSizeID))
Me!SoldSizeType = DLookup("[SizeType]", "Sizes", "[SizeID] = " &
longSizeID)
msingleConversionFactor = DLookup("[SizeStockUOMConversion]", "Sizes",
"
[SizeID] = " & longSizeID)
mstrAltUOM = DLookup("[SizeStockAltUOM]", "Sizes", "[SizeID] = " &
longSizeID)
If IsNull(mstrAltUOM) Or IsEmpty(mstrAltUOM) Or mstrAltUOM = "" Then
Me!SoldAltUOM.Enabled = False
Me!InvoiceLineQtySold.Enabled = False
Else
Me!SoldAltQty = Me!InvoiceLineQtySold * msingleConversionFactor
End If
If IsNull(DLookup("[StockShop]", "Stock", ("[StockSizeID] = " &
longSizeID & " AND [StockSiteCode] = """ & mstrSiteCode & """"))) Then
msingleTotalStock = 0
msingleStockAvailable = 0
Me!InvoiceLineUnitPrice = 0
Else
msingleTotalStock = DLookup("[StockShop]", "Stock", ("[StockSizeID]
=
" & longSizeID & " AND [StockSiteCode] = """ & mstrSiteCode & """"))
msingleTotalStock = msingleTotalStock + DLookup("[StockBack]",
"Stock", ("[StockSizeID] = " & longSizeID & " AND [StockSiteCode] = """ &
mstrSiteCode & """"))
msingleStockAvailable = msingleTotalStock -
DLookup("[StockAllocated]
", "Stock", ("[StockSizeID] = " & longSizeID & " AND [StockSiteCode] = """
&
mstrSiteCode & """"))
Me!InvoiceLineUnitPrice = DLookup("[StockSellPrice]", "Stock", ("
[StockSizeID] = " & longSizeID & " AND [StockSiteCode] = """ &
mstrSiteCode &
""""))
End If
Me!SoldUOM = DLookup("[SizeStockUOM]", "Sizes", "[SizeID] = " &
longSizeID)
Me!SoldAltUOM = mstrAltUOM
Call Price_line
Me!Msg15_result = " "
Me!Msg16_result = " "
Me!InvoiceLineQtySold.SetFocus

End Sub

... and didn't I have fun with those Dlookups and the number of
double-quote
marks! It was another of your posts that put me right on that point, so
thanks for that guidance, too.
Some information about the calls:
GETBASICDATA is a sub that opens a separate form to allow the user to
select
product and size (size being a subordinate to product -- think different
sizes of cans of beans, for example), and returns both product and size
IDs.
PRICE_LINE merely calculates the extension unit price * quantity.
Variables prefixed with "m" are Dim-ed at module level.
Hope this isn't information overload!

Brian.

Brian, open your subform in design view.
Right-click the Product, and choose Properties.
On the Data tab, what do you have for Control Source?
It should be the name of the field where the product number is stored.
If it is unbound, it will be the same on every row of the continuous form
(including the new row.)

If that is not the issue, do you have any code in the events of this
control, or of the form? Anything that would assign a value or Default
Value
to it?

(BTW, you don't need the Save in the LostFocus of the last control. Access
will save it automatically at that point.)

I'm "learning by doing", so it's likely that I've done something
obviously
wrong but can't see it - so here's a cry for help. Situation: Form

[quoted text clipped - 34 lines]

probably been looking at it for hours and not recognised it.
Brian.

 

[bdp via AccessMonster.com]

Allen,

In normal use, the product's barcode is scanned into the "Barcode" field.
That barcode identifies uniquely the "thing" (in reality, a combination of
"Product" and "Size") that's been sold and therefore needs to appear in the
invoice line. Leaving that field, whether by tab or mouse action, triggers
"LostFocus" and therefore fills in most of the remaining data on the form,
which comes variously from the "Sizes" (effectively, products) and "Stock"
tables, and leaving the user to key in the quantity (which defaults to 1 unit,
for what that's worth). I can see that AfterUpdate would do the same thing.
At the time, I adopted the "why? why not?" approach and used LostFocus
rather than AfterUpdate, assuming that there's no real difference in the
situation where each new record would be identified with a different barcode.
That control will have an entry for each "thing" sold, and I can't see a
distinction between the two events in terms of which to use for the purpose.
Is this where I've gone wrong, do you think (and what's the distinction that
I've failed to understand)?

Brian.

Without going through your code in detail, why LostFous?

(remainder of quoted text clipped).

 

[Allen Browne]

No, what I'm suggesting is that the control's LostFocus event may be firing
unnecessarily as soon as you move into the new row, which triggers all that
stuff which gives you the problems I think you are trying to avoid when you
move into the new row.

So if you you moved the code into the control's Afterupdate event, it would
still fire after a scan, but not simply by moving through the control?

Or have I missed the point?

 

[bdp via AccessMonster.com]

Allen,

I tried it, and it didn't make any difference. The symptoms I described in
the original post are reproduced exactly when the code is moved to the sub
BarcodeAfter_Update, and the BarcodeLost_Focus sub ceases to exist.

My suspicion is that I've done something silly in another place ... but I
don't know enough to detect where that other place might be. Others must be
able to make this approach work successfully - I assume (dangerous, I know)
this is the correct approach, to use a continuous subform to add records to a
child table; I just haven't found the right magic wand to get it working.
Should I question the whole premise of what I'm doing? Is it perhaps not the
right approach to try adding child records through a subform - should I be
creating a separate single form and using a recordsetclone technique to add a
new record, then requery the subform to show the updated list of child
records? I've noted some suggestions in other posts that this is the proper
way to do it, but I didn't read into them that it was the only way to do it.

I have a feeling that the answer will turn out to be very simple, glaringly
obvious, and something that I've misunderstood. If I knew the right question
to ask myself ... we'd both wonder how we missed the point!

Going off the air until tomorrow morning Oz time.

Brian.

No, what I'm suggesting is that the control's LostFocus event may be firing
unnecessarily as soon as you move into the new row, which triggers all that
stuff which gives you the problems I think you are trying to avoid when you
move into the new row.

So if you you moved the code into the control's Afterupdate event, it would
still fire after a scan, but not simply by moving through the control?

Or have I missed the point?

Allen,

[quoted text clipped - 26 lines]

(remainder of quoted text clipped).

 

[Allen Browne]

You are using a subform exactly as intended. A main form bound to a primary
table, with a subform bound to a related table is standard fare in Access.

As you say, something else must be interferring. We need to simplify it
down, removing anything that doesn't have to be there. Make a copy of the
database, and delete all the code in both modules (the main form and
subform.) Remove input masks, formats -- anything that could mask what's
going on. Once the form and subform are working correctly, you can then
start adding things again.

Get some sleep. No soccer tonight.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried it, and it didn't make any difference. The symptoms I described
in
the original post are reproduced exactly when the code is moved to the sub
BarcodeAfter_Update, and the BarcodeLost_Focus sub ceases to exist.

My suspicion is that I've done something silly in another place ... but I
don't know enough to detect where that other place might be. Others must
be
able to make this approach work successfully - I assume (dangerous, I
know)
this is the correct approach, to use a continuous subform to add records
to a
child table; I just haven't found the right magic wand to get it working.
Should I question the whole premise of what I'm doing? Is it perhaps not
the
right approach to try adding child records through a subform - should I be
creating a separate single form and using a recordsetclone technique to
add a
new record, then requery the subform to show the updated list of child
records? I've noted some suggestions in other posts that this is the
proper
way to do it, but I didn't read into them that it was the only way to do
it.

I have a feeling that the answer will turn out to be very simple,
glaringly
obvious, and something that I've misunderstood. If I knew the right
question
to ask myself ... we'd both wonder how we missed the point!

Going off the air until tomorrow morning Oz time.

Brian.

No, what I'm suggesting is that the control's LostFocus event may be
firing
unnecessarily as soon as you move into the new row, which triggers all
that
stuff which gives you the problems I think you are trying to avoid when
you
move into the new row.

So if you you moved the code into the control's Afterupdate event, it
would
still fire after a scan, but not simply by moving through the control?

Or have I missed the point?

Allen,

[quoted text clipped - 26 lines]

(remainder of quoted text clipped).

 

[bdp via AccessMonster.com]

No soccer ... bliss! No cricket, either ... gloom!

Wise advice: when in doubt, return to basics and take simple steps until the
problem reappears ... which it did. When I extended the subform (invoice
lines) to two lines on the screen, one to contain the barcode and its
translation into human-recognisable product identification, and the next to
contain the details of what had been sold, the behaviour that I noted in the
original post returned.

The question, therefore, becomes "Is there a prohibition on having a
continuous form spread one row of data over two (or more, I suppose, to be
general) lines on the screen?" Putting the two lines back together
(impractical to read on screen, but it proves the point) has no effect on the
problem, so the code that deals with capturing and translating the barcode
seems to be the culprit. That was the stuff that I posted earlier in the
piece. I shall now backtrack on that, rebuilding it from the ground up to
see at what point nonsense appears.

I may not get this done today - lots of other things demand time - and then
we'll be away for a little while, so the final resolution may not get itself
posted straight off. Nonetheless, it will appear once I've reached certainty.


Thanks, Allen - I appreciate your assistance.

You are using a subform exactly as intended. A main form bound to a primary
table, with a subform bound to a related table is standard fare in Access.

As you say, something else must be interferring. We need to simplify it
down, removing anything that doesn't have to be there. Make a copy of the
database, and delete all the code in both modules (the main form and
subform.) Remove input masks, formats -- anything that could mask what's
going on. Once the form and subform are working correctly, you can then
start adding things again.

Get some sleep. No soccer tonight.

I tried it, and it didn't make any difference. The symptoms I described
in

[quoted text clipped - 49 lines]

 

[bdp via AccessMonster.com]

Allen,
A bit more progress. I started with the invoice lines form containing only
controls bound to the invoice lines table. No problem. I included extra
controls that would, in due course, become the barcode and translation, but
left them unbound and the subject of no code. Again, no problem. Then I
reinstated a part of the BarcodeAfter_Update sub that I included in an early
post - this part:

Private Sub Barcode_AfterUpdate()
Dim strBarcode As String
Dim longSizeID As Long
Dim strSiteCode As String
Dim longProductID As Long
Dim boolFoundit As Boolean

mstrSiteCode = Parent!SiteCode
strBarcode = Me!Barcode
If Not (IsNull(strBarcode) Or IsEmpty(strBarcode) Or strBarcode = "" _
Or strBarcode = "?") Then
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "Select * from Barcodes where Barcode = """ &
strBarcode & """", CurrentProject.Connection, adOpenStatic
longSizeID = rst!BarcodeSizeID
rst.Close
Set rst = Nothing
Else
boolFoundit = GetBasicData(longProductID, longSizeID, "S")
DoCmd.Close acForm, "FormFind_SelectProduct"
If Not boolFoundit Then
Exit Sub
End If
End If
Me!InvoiceLineSizeID = longSizeID
Me!SoldProductName = DLookup("[ProductName]", "[Products]", "[ProductID]
= " & DLookup("[ProductID]", "Sizes", "[SizeID] = " & longSizeID))
Me!SoldSizeType = DLookup("[SizeType]", "Sizes", "[SizeID] = " &
longSizeID)
End Sub

(The "Else" code isn't executed in my tests). At this point the problem
resurfaced, in a slightly different form. I can now add multiple invoice
lines quite happily; but whenever I enter a barcode in the "new" record, all
others on the screen change to contain the same barcode and product
translation data (me!SoldProductName and me!SoldSizeType). The SizeID (part
of the PK in the invoice lines table) remains unaltered

So the question now is: Why is the data that I enter to a new line in the
subform replicated into all other lines displayed in that subform, while the
underlying table contains the correct data? I suspect the problem lies in
the last two lines - but why do they affect all the lines visible on the
screen, without changing the contents of the underlying table? Do I need to
identify a particular row that I'm creating or maintaining? If so, how do I
do that?

Brian.

You are using a subform exactly as intended. A main form bound to a primary
table, with a subform bound to a related table is standard fare in Access.

As you say, something else must be interferring. We need to simplify it
down, removing anything that doesn't have to be there. Make a copy of the
database, and delete all the code in both modules (the main form and
subform.) Remove input masks, formats -- anything that could mask what's
going on. Once the form and subform are working correctly, you can then
start adding things again.

Get some sleep. No soccer tonight.

I tried it, and it didn't make any difference. The symptoms I described
in

[quoted text clipped - 49 lines]

 

[Allen Browne]

Brian, I was trying to avoid analyzing this code, since I suspect that none
of it does what you expect. I am wondering if you have disabled error
handling, as the potential for errors is quite high.

The line:
mstrSiteCode = Parent!SiteCode
presumably refers to a module level string variable. This line will fail
when the SiteCode is null (e.g. main form at a new record), since a string
variable cannot be null. Should this be a Variant?

The line:
strBarcode = Me!Barcode
could fail for the same reason. Should this be a variant again?

Unless you change it to a Variant, the line:
If Not (IsNull(strBarcode) Or IsEmpty(strBarcode) _
Or strBarcode = "" Or strBarcode = "?") Then
can be abbreviated to:
If Not (strBarcode = "" Or strBarcode = "?") Then
since a string cannot be null (applies to a field without a value) or empty
(applies to an uninitialized Variant.)

I am not sure what the Recordset is for. If it returns no records, the
attempt to set lngSizeID to the value from a non-existent record will fail.
If there is a record, but the BarcodeSizeID is null, the attempt to set
lngSizeID will also fail. Could all this be replaced with a DLookup()?

We don't know much of what the Else block would do.

The last 3 lines should not cause the problem you describe.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
A bit more progress. I started with the invoice lines form containing
only
controls bound to the invoice lines table. No problem. I included extra
controls that would, in due course, become the barcode and translation,
but
left them unbound and the subject of no code. Again, no problem. Then I
reinstated a part of the BarcodeAfter_Update sub that I included in an
early
post - this part:

Private Sub Barcode_AfterUpdate()
Dim strBarcode As String
Dim longSizeID As Long
Dim strSiteCode As String
Dim longProductID As Long
Dim boolFoundit As Boolean

mstrSiteCode = Parent!SiteCode
strBarcode = Me!Barcode
If Not (IsNull(strBarcode) Or IsEmpty(strBarcode) Or strBarcode = "" _
Or strBarcode = "?") Then
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "Select * from Barcodes where Barcode = """ &
strBarcode & """", CurrentProject.Connection, adOpenStatic
longSizeID = rst!BarcodeSizeID
rst.Close
Set rst = Nothing
Else
boolFoundit = GetBasicData(longProductID, longSizeID, "S")
DoCmd.Close acForm, "FormFind_SelectProduct"
If Not boolFoundit Then
Exit Sub
End If
End If
Me!InvoiceLineSizeID = longSizeID
Me!SoldProductName = DLookup("[ProductName]", "[Products]",
"[ProductID]
= " & DLookup("[ProductID]", "Sizes", "[SizeID] = " & longSizeID))
Me!SoldSizeType = DLookup("[SizeType]", "Sizes", "[SizeID] = " &
longSizeID)
End Sub

(The "Else" code isn't executed in my tests). At this point the problem
resurfaced, in a slightly different form. I can now add multiple invoice
lines quite happily; but whenever I enter a barcode in the "new" record,
all
others on the screen change to contain the same barcode and product
translation data (me!SoldProductName and me!SoldSizeType). The SizeID
(part
of the PK in the invoice lines table) remains unaltered

So the question now is: Why is the data that I enter to a new line in the
subform replicated into all other lines displayed in that subform, while
the
underlying table contains the correct data? I suspect the problem lies in
the last two lines - but why do they affect all the lines visible on the
screen, without changing the contents of the underlying table? Do I need
to
identify a particular row that I'm creating or maintaining? If so, how do
I
do that?

Brian.

You are using a subform exactly as intended. A main form bound to a
primary
table, with a subform bound to a related table is standard fare in Access.

As you say, something else must be interferring. We need to simplify it
down, removing anything that doesn't have to be there. Make a copy of the
database, and delete all the code in both modules (the main form and
subform.) Remove input masks, formats -- anything that could mask what's
going on. Once the form and subform are working correctly, you can then
start adding things again.

Get some sleep. No soccer tonight.

I tried it, and it didn't make any difference. The symptoms I described
in

[quoted text clipped - 49 lines]

(remainder of quoted text clipped).

 

[bdp via AccessMonster.com]

Allen,

Answers interspersed.

Brian, I was trying to avoid analyzing this code, since I suspect that none
of it does what you expect. I am wondering if you have disabled error
handling, as the potential for errors is quite high.

You're right - I haven't made any specific provision for error handling in
this sub. I've put in the standard error trap that appears with the wizard-
generated code for command buttons - it identifies what's going on, at least.
That was a major blunder or my part - thanks for pointing it out. Having
then re-tested the form, no errors are thrown up by the inclusion, and apart
from the fact that it's replicating the barcode translation data (productname
and sizetype) in all lines of the subform, which is definitely not what I
want or intend, it's doing just what I expect - taking a barcode entered by
the user, obtaining the size ID for that barcode from the Barcodes table (I'm
still using the recordset at this stage, but see remarks further on), finding
and displaying the product to which that size belongs, and identifying the
size type.

The line:
mstrSiteCode = Parent!SiteCode
presumably refers to a module level string variable. This line will fail
when the SiteCode is null (e.g. main form at a new record), since a string
variable cannot be null. Should this be a Variant?

It does. The main form contains a control [SiteCode] that's defined as a
Dlookup on a one-record control table (which carries various other
information about the site - things like last-used record numbers in site-
specific tables), so it's initialised when the main form is loaded. I don't
think that, in practice, it's high-risk, unless the control table is removed;
that's one of the first things that the app. has to have created when it's
installed, and once created, it's not accessed consciously by the user. I
can understand that, without that knowledge, it looks hazardous. I'm relaxed.

The line:
strBarcode = Me!Barcode
could fail for the same reason. Should this be a variant again?

This is in the BarcodeAfter_Update sub, so only gets executed when the
Barcode has been changed. If I were to tab into (or click on) an otherwise
empty barcode control, then tab or click out of it, would the After_Update
event fire? I thought not, from your remarks earlier in the thread. However,
defining it as a variant is easy; I made the change but it hasn't had any
impact on the problem in hand.

Unless you change it to a Variant, the line:
If Not (IsNull(strBarcode) Or IsEmpty(strBarcode) _
Or strBarcode = "" Or strBarcode = "?") Then
can be abbreviated to:
If Not (strBarcode = "" Or strBarcode = "?") Then
since a string cannot be null (applies to a field without a value) or empty
(applies to an uninitialized Variant.)

No change, since I've changed strBarcode to be varBarcode - but thanks for
the clarification.

I am not sure what the Recordset is for. If it returns no records, the
attempt to set lngSizeID to the value from a non-existent record will fail.
If there is a record, but the BarcodeSizeID is null, the attempt to set
lngSizeID will also fail. Could all this be replaced with a DLookup()?

The recordset is to return a record that links the barcode to a size ID
(that's what we need to get at a stock record, along with the SiteCode
discussed above). The barcodes table has both barcode and size ID as
required fields, so it shouldn't be possible for the second case to happen -
but the first might, despite my intentions elsewhere to create barcodes if
none are supplied, and to allow multiple barcodes for the same size ID (you
may have noticed that on some books, for example). Would a DLookup do the
job just as well? What would happen if there were no record? Would it be
more robust to test the recordcount to see whether there's a record, and take
appropriate avoiding action if there isn't?

That said, in my testing which produced the problems that still appear, I was
using barcodes that existed in the Barcodes table, and which returned valid
size IDs - so while you're right about the potential for disaster, and thanks
for pointing that out, I don't think we can treat it as the cause of the
problem.

We don't know much of what the Else block would do.

.... and it wasn't invoked in my testing anyway. I explained it in an earlier
post (but perhaps there was too much information there!).

Progress ... nil (sounds like that soccer again!)

Brian.

The last 3 lines should not cause the problem you describe.

Allen,
A bit more progress. I started with the invoice lines form containing

[quoted text clipped - 80 lines]

 

[Allen Browne]

Brian, I don't know what to suggest.

The assignment at the end of your code should be applied to the current
record only (not previous records.)

A control can be null in its AfterUpdate (typically when the user backspaces
all characters out.)

The record count is not ideal in ADO, as it is -1 (undetermined) for some
types. DLookup() should be fine.

Perhaps you can simplify this routine (temporarily) to just one line. In
BarCode_AfterUpdate just:
Me!InvoiceLineSizeID = 10
and see if that happens on all lines, even when you type something in.
If so, the control has to be unbound at that point.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Answers interspersed.

Brian, I was trying to avoid analyzing this code, since I suspect that
none
of it does what you expect. I am wondering if you have disabled error
handling, as the potential for errors is quite high.

You're right - I haven't made any specific provision for error handling in
this sub. I've put in the standard error trap that appears with the
wizard-
generated code for command buttons - it identifies what's going on, at
least.
That was a major blunder or my part - thanks for pointing it out. Having
then re-tested the form, no errors are thrown up by the inclusion, and
apart
from the fact that it's replicating the barcode translation data
(productname
and sizetype) in all lines of the subform, which is definitely not what I
want or intend, it's doing just what I expect - taking a barcode entered
by
the user, obtaining the size ID for that barcode from the Barcodes table
(I'm
still using the recordset at this stage, but see remarks further on),
finding
and displaying the product to which that size belongs, and identifying the
size type.

The line:
mstrSiteCode = Parent!SiteCode
presumably refers to a module level string variable. This line will fail
when the SiteCode is null (e.g. main form at a new record), since a string
variable cannot be null. Should this be a Variant?

It does. The main form contains a control [SiteCode] that's defined as a
Dlookup on a one-record control table (which carries various other
information about the site - things like last-used record numbers in site-
specific tables), so it's initialised when the main form is loaded. I
don't
think that, in practice, it's high-risk, unless the control table is
removed;
that's one of the first things that the app. has to have created when it's
installed, and once created, it's not accessed consciously by the user. I
can understand that, without that knowledge, it looks hazardous. I'm
relaxed.

The line:
strBarcode = Me!Barcode
could fail for the same reason. Should this be a variant again?

This is in the BarcodeAfter_Update sub, so only gets executed when the
Barcode has been changed. If I were to tab into (or click on) an
otherwise
empty barcode control, then tab or click out of it, would the After_Update
event fire? I thought not, from your remarks earlier in the thread.
However,
defining it as a variant is easy; I made the change but it hasn't had any
impact on the problem in hand.

Unless you change it to a Variant, the line:
If Not (IsNull(strBarcode) Or IsEmpty(strBarcode) _
Or strBarcode = "" Or strBarcode = "?") Then
can be abbreviated to:
If Not (strBarcode = "" Or strBarcode = "?") Then
since a string cannot be null (applies to a field without a value) or
empty
(applies to an uninitialized Variant.)

No change, since I've changed strBarcode to be varBarcode - but thanks for
the clarification.

I am not sure what the Recordset is for. If it returns no records, the
attempt to set lngSizeID to the value from a non-existent record will
fail.
If there is a record, but the BarcodeSizeID is null, the attempt to set
lngSizeID will also fail. Could all this be replaced with a DLookup()?

The recordset is to return a record that links the barcode to a size ID
(that's what we need to get at a stock record, along with the SiteCode
discussed above). The barcodes table has both barcode and size ID as
required fields, so it shouldn't be possible for the second case to
happen -
but the first might, despite my intentions elsewhere to create barcodes if
none are supplied, and to allow multiple barcodes for the same size ID
(you
may have noticed that on some books, for example). Would a DLookup do the
job just as well? What would happen if there were no record? Would it be
more robust to test the recordcount to see whether there's a record, and
take
appropriate avoiding action if there isn't?

That said, in my testing which produced the problems that still appear, I
was
using barcodes that existed in the Barcodes table, and which returned
valid
size IDs - so while you're right about the potential for disaster, and
thanks
for pointing that out, I don't think we can treat it as the cause of the
problem.

We don't know much of what the Else block would do.

... and it wasn't invoked in my testing anyway. I explained it in an
earlier
post (but perhaps there was too much information there!).

Progress ... nil (sounds like that soccer again!)

 

[bdp via AccessMonster.com]

Allen,

I take the point about users backspacing. I've made the conversion to
variant, so I should be protected against that mishap.

I note that Dlookup returns null if there's no record - I'll make that change.
It looks easier than using a recordset, and I like easy.

I changed Barcode_AfterUpdate as you suggested, and sure enough the other
barcode and translation controls changed. Yes, they are unbound, since those
controls don't relate to any of the fields in the table on which the subform
is based. So, is that the reason why all instances of the translated fields
are changed in sync with each other?

If that's the case, what (if anything) can I do to make those controls on the
screen continue to show variously what the user entered (the barcode itself)
and what that means (the productname and sizetype that are translated from
the barcode)? Can I, for instance, do something to lock a line in the
subform once it's been accepted, while leaving the "new" record's line open
for edit? I thought - briefly - of putting a Dlookup in the control source
of the translated fields, but then realised that, if the barcode on which
such a Dlookup would be based is going to change each time the user scans
something, that'll change the Dlookup result just as effectively as the
present approach of setting their values in the AfterUpdate code.

I'd have thought this is something that many developers would want to do, in
different applications ... is there a way?

Brian.

Brian, I don't know what to suggest.

The assignment at the end of your code should be applied to the current
record only (not previous records.)

A control can be null in its AfterUpdate (typically when the user backspaces
all characters out.)

The record count is not ideal in ADO, as it is -1 (undetermined) for some
types. DLookup() should be fine.

Perhaps you can simplify this routine (temporarily) to just one line. In
BarCode_AfterUpdate just:
Me!InvoiceLineSizeID = 10
and see if that happens on all lines, even when you type something in.
If so, the control has to be unbound at that point.

Allen,

[quoted text clipped - 104 lines]

Progress ... nil (sounds like that soccer again!)

 

[Allen Browne]

Okay, finally. The problem controls *are* unbound.
I thought we dealt with that in the first reply.

Unbound controls always show the same value in all rows of a
datasheet/continuous form.

Workarounds:
a) Create a query to use as the Record Source of the subform.
Include the other tables in the query, so you can display fields from those
tables too.

b) Use combo boxes, so the RowSource can get the values from the other
tables, and the bound column can be zero-width.

c) Use DLookup() expressions. (slow.)

 

[bdp via AccessMonster.com]

Allen,

Problem solved - thank you! Until this happened, I hadn't appreciated that
characteristic of continuous forms.

We did deal with it in the first reply, but terminology shifted a bit through
the discussion. I'd used "product" originally to refer to what I've been
calling Size ID recently (the concept of Size vs Product was one that I
though would serve only to confuse rather than to illuminate), but as we got
down to pieces of code, "Size" appeared and had to be used. My apologies; I
should have been clearer in communicating, and thanks for bearing with me so
patiently.

I'll experiment with both the query and the combo box approach, to see which
gives the better look and feel (highly subjective, of course!) for the user.

Allen, I'm deeply grateful to you for sticking with a problem that I thought
was well-defined but which, in the end, I hadn't defined adequately. Thanks
for all your help. As we say in Oz, your blood should be bottled (though
what you do with it then is a matter of choice).

Brian.