Access form defaul value from other box and preventing wrong product

[Hursan]

I have a form/query, on click this form saves the data to a table and then a query is run updating the records. We use this to mark products that have been shipped as "shipped". I am having 2 issues.

user enters DeID Base (Account) then the bag/box number, then the user needs to enter the the product shipped witch starts with the DeID + 4 numbers usually starting with 1001. I have the DeID and Box set to default = to the de ID base and box previously entered but the table does not populate the first records but does in the second. Screen shot in yellow.


I want a pop up warning if the user enters one DeID but by accident scans a different product for a different customer. Like the DEID is 1001ABC and the user scans a product with code 1234ABC1001, which should be going to client 1234ABC. Example in screenshoot in blue.

[kd2017]

I have a form/query, on click this form saves the data to a table and then a query is run updating the records. We use this to mark products that have been shipped as "shipped". I am having 2 issues.

user enters DeID Base (Account) then the bag/box number, then the user needs to enter the the product shipped witch starts with the DeID + 4 numbers usually starting with 1001. I have the DeID and Box set to default = to the de ID base and box previously entered but the table does not populate the first records but does in the second. Screen shot in yellow.

Just to be crystal clear: when you say default value do you mean the controls actual default value property? You said you run a query to update records, so I think you mean these records are updated in the table

I want a pop up warning if the user enters one DeID but by accident scans a different product for a different customer. Like the DEID is 1001ABC and the user scans a product with code 1234ABC1001, which should be going to client 1234ABC. Example in screenshoot in blue.

[psuedo code]

'before update
if left(Me.DeID, 7) <> Me.DeID_Base then
    msgbox "ABORT! ABORT! ABORT! WEEOO WEEOO WEEOO"
    cancel = true
end if

Can you provide a screenshot of the table relationship view and provide the sql to the update query you mentioned? And the relevant code for the primary form button?

[Hursan]

By default I mean the text box properties defaul value is set to the Deid and Bag/box values see screen shoot.





This is the code for the ship and new buttom:
Private Sub BtmShipSamples_Click()

DoCmd.OpenQuery "Shipping_Query"

CurrentDb.Execute "Delete * From ShippingTabe"

TxbDeIDBox.Value = ""

TxbBagBox.Value = ""

Me.Refresh

End Sub

This is the query SQL code, relationship table attached.


UPDATE SR_Information_Target_Specimen INNER JOIN ShippingTabe ON (SR_Information_Target_Specimen.DeIDBased = ShippingTabe.DeIDBase) AND (SR_Information_Target_Specimen.DeID = ShippingTabe.DeID) SET SR_Information_Target_Specimen.Location = [ShippingTabe]![Location], SR_Information_Target_Specimen.ShipDate = [ShippingTabe]![Time Modified]
WHERE (((SR_Information_Target_Specimen.DeIDBased)=[ShippingTabe]![DeIDBase]) AND ((SR_Information_Target_Specimen.DeID)=[ShippingTabe]![DeID]) AND ((ShippingTabe.[Bag/Box])=[ShippingTabe]![Bag/Box]));
Private Sub BtmShipSamples_Click()

DoCmd.OpenQuery "Shipping_Query"

CurrentDb.Execute "Delete * From ShippingTabe"

TxbDeIDBox.Value = ""

TxbBagBox.Value = ""

Me.Refresh

End Sub

[Hursan]

Attachment 47836Attachment 47836Attachment 47837

[Hursan]

By default I mean the text box properties defaul value is set to the Deid and Bag/box values see screen shoot.

Name: 20220518_173557.jpg
Views: 0
Size: 93.3 KB


Name: 20220518_173605.jpg
Views: 0
Size: 100.1 KB


This is the code for the ship and new buttom:
Private Sub BtmShipSamples_Click()


DoCmd.OpenQuery "Shipping_Query"


CurrentDb.Execute "Delete * From ShippingTabe"


TxbDeIDBox.Value = ""


TxbBagBox.Value = ""


Me.Refresh


End Sub


This is the query SQL code, relationship table attached.


Name: Query.PNG
Views: 0
Size: 29.9 KB
UPDATE SR_Information_Target_Specimen INNER JOIN ShippingTabe ON (SR_Information_Target_Specimen.DeIDBased = ShippingTabe.DeIDBase) AND (SR_Information_Target_Specimen.DeID = ShippingTabe.DeID) SET SR_Information_Target_Specimen.Location = [ShippingTabe]![Location], SR_Information_Target_Specimen.ShipDate = [ShippingTabe]![Time Modified]
WHERE (((SR_Information_Target_Specimen.DeIDBased)=[ShippingTabe]![DeIDBase]) AND ((SR_Information_Target_Specimen.DeID)=[ShippingTabe]![DeID]) AND ((ShippingTabe.[Bag/Box])=[ShippingTabe]![Bag/Box]));
Private Sub BtmShipSamples_Click()


DoCmd.OpenQuery "Shipping_Query"


CurrentDb.Execute "Delete * From ShippingTabe"


TxbDeIDBox.Value = ""


TxbBagBox.Value = ""


Me.Refresh


End Sub

[ssanfu]

In Post #4, none of the attachments are valid. See "How to attach files" at the top in the tool bar.

[Hursan]

The screenshoot/attachments are included with post #3, post 4 and 5 were accidents

[kd2017]

but the table does not populate the first records but does in the second. Screen shot in yellow.

Okay. So what *I think* is happening is that when the subform loads, it does see that the default values should be [x] from the parent form however if the textboxes are blank then the default values are blank. Whats more, when you are loading a form with subforms the subfrom loads first, then the parent form. What this means is that even if you intended for the parent form's controls to load with values (then the subform should see those) that they wont load until after the subform is loaded, so the subform will still load blanks.

After you've loaded your forms and started adding records to the subform only then is it able to see that there are values in the parent form controls to copy over as default values to the new records.

I think you're just going to have to find a workaround. My initial suggestion is to have add vba code in the subform's before update event that will check for blank values in bag/box and deldbase and copy over the parent form's values if needed, you'll run the code to validate the De-ID here as well.

Note that you shouldn't put special characters or spaces in names of objects (tables, fields, forms, controls, etc.), instead of spaces use underscores or camel case notation. For example I'd would change the field name [Bag/Box] to [bag_box], or [bagBox], or [BagBox]. What ever convention you chose stick with it and be consistent, it'll make life easier down the road.

[Gicu]

First of all when you clear a control on a form you set it to null not ZLS.
So

TxbDeIDBox.Value = ""
TxbBagBox.Value = ""

should be:

Me.TxbDeIDBox = Null 'added Me. for clarity and removed .Value as that is the default property
Me.TxbBagBox = Null

After running the delete statement you should also try to requery the subform

Me.sfrmYourSubform.Form.Requery

Cheers,