lookup and populate Datasheet with editable selected field, save to other table (1 Viewer)

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
good day,

I am here again seeking help for my db... As per title, is this possible?

What I am trying to achieve is an update for the purchase request, becoming a purchase order.

I was hoping that in purchase order form I will only input the purchase request number as a lookup and the textboxes in the form for the fields will be populated as what is saved in TblPurchaseRequest.

The Difficult part now is that I am trying to add a Datasheet subform in the Purchase order form where the lookup will be from Purchase Request number and LOT number. The datasheet will be populated with the data depending on what LOT in that Purchase Request like Unit, Item Description, quantity and cost, but the Quantity and cost will be editable as that value is subject to change in Purchase Order.

after all the data after lookup and being edited, I was also hoping that being the lookup, it will not affect the data from what table is it from, but will be saved in the different table like TblPurchaseOrder, TblPOLot, and TblPOLotDetails.

Prior to this post, I have been searching the net, but I am stuck at populating the datasheet subform with editable value and saved in different table...

I am sorry for bothering but I am in desperate need of expert help for the db, this is actually my 1st db, I migrated to access because excel has limitation and loads slow when there are already to many tables, data, automation computations..

Thank you in Advance...
 

Attachments

  • william2 - Copy.zip
    281.2 KB · Views: 71

bastanu

AWF VIP
Local time
Today, 14:08
Joined
Apr 13, 2010
Messages
1,174
Hi William,

It is doable for sure, you just need create the "other" table as per your requirements. Lets say you select a purchase request in a multi-column combo box that shows you the purchase request number or ID followed by the details that you mention. In the AfterUpdate event of that combobox you need to add code to populate the various fields in the datasheet form (bound to your new tblPurchaseOrderDetails table) using references to the combo-box's column collection - assuming the combobox .column(0) is the bound column (PurchaseRequestID or #):

Me.txtItem= Me.cboPurchaseRequest.column(1)
Me.txtUnit= Me.cboPurchaseRequest.column(2)
Me.txtQuantity= Me.cboPurchaseRequest.column(3)
Me.txtPrice= Me.cboPurchaseRequest.column(4)

So what you are doing is populating the controls from the "lookup" ( = combo-box row source) but leaving them editable.

If you need more help please post back with more specifics (form/subform names, table names, etc.).

Cheers,
Vlad
 

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
Thank you for the reply, will try to analyze this advice and try to apply...
Will get back if I failed to apply...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:08
Joined
Oct 29, 2018
Messages
19,002
Hello. Pardon me for jumping in but storing duplicate information in multiple tables is usually not recommended. Wouldn’t it be sufficient to simply add a status field to the same table to indicate if the record is a purchase request or an order? In some cases, using an Order Date field is enough.
 

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
Hello. Pardon me for jumping in but storing duplicate information in multiple tables is usually not recommended. Wouldn’t it be sufficient to simply add a status field to the same table to indicate if the record is a purchase request or an order? In some cases, using an Order Date field is enough.

Hello sir,

May you advice me on how to do it?
May I also seek help on my TblLotParticular, I made relationship on PRID but in the table, PRID is blank...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:08
Joined
Oct 29, 2018
Messages
19,002
Hi. How I might create a database like yours is to have only one table for purchase orders (unless you need details, then it'll be two tables). Each record in the purchase orders table would have a status field to indicate the current condition of the order, for example, requested, approved, filled, cancelled, etc. This would avoid having to move records around in different tables. Regarding tblLotParticular and PRID, first, you're using a lookup field at the table level, which most developers recommend avoiding. Second, I am not sure I understand your question. You should be able to establish a relationship even if the field is Null. If you enforce referential integrity, what you cannot do is assign a non-existing value - but no value should be okay.
 

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
Good day,

Is it possible to have 2 or more foreign key in 1 table? I am talking about a foreign key that is not created by lookup, but by relationships, where when I enter data, it will be automatically valued in the assigned table..

My concerned is in TblLotParticular, foreign keys are PRID, LotID, POID. I gave up on making the PRID appear in this table, but I wanted the POID be reflected because it is an update for the Purchase Request being approved as Purchase Order, where I will record POQuantity and POCost. I put them in 1 table to I can lookup using combobox, combobox1 as PRID, then combobox2 will only display LotID based in PRID. then the subform will display details for the LotID.
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    94.5 KB · Views: 59

theDBguy

I’m here to help
Staff member
Local time
Today, 14:08
Joined
Oct 29, 2018
Messages
19,002
Hi. Yes, you can have as many FK fields in a table as you want. However, auto-populating them is another matter.
 

bastanu

AWF VIP
Local time
Today, 14:08
Joined
Apr 13, 2010
Messages
1,174
One way to "auto-populate" a foreign key is by using a form/subform combination. Or look at using data macros but those are usually a hit and miss.

Cheers,
Vlad
 

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
Good day,

I have attached my db... so far it is where is have accomplished so far...
 

Attachments

  • William - Copy.zip
    309.2 KB · Views: 46

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
you might notice that the POID in the TblLotParticular is in lookup, it was because I am trying to replace it coz the 1st time I made it just a number, access does not auto input the POID from TblPurchaseOrder form...
 

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
good day, I am confused with two advices from the masters, but I would like to prefer theDBguy since it will save file size.

I have tried applying it, then link relationships to IDs, but after data is entered, the related field has no input or value... I am just worried because I might not be able to reference it in report without the values in the IDs

Please help...

Hi William,

It is doable for sure, you just need create the "other" table as per your requirements. Lets say you select a purchase request in a multi-column combo box that shows you the purchase request number or ID followed by the details that you mention. In the AfterUpdate event of that combobox you need to add code to populate the various fields in the datasheet form (bound to your new tblPurchaseOrderDetails table) using references to the combo-box's column collection - assuming the combobox .column(0) is the bound column (PurchaseRequestID or #):

Me.txtItem= Me.cboPurchaseRequest.column(1)
Me.txtUnit= Me.cboPurchaseRequest.column(2)
Me.txtQuantity= Me.cboPurchaseRequest.column(3)
Me.txtPrice= Me.cboPurchaseRequest.column(4)

So what you are doing is populating the controls from the "lookup" ( = combo-box row source) but leaving them editable.

If you need more help please post back with more specifics (form/subform names, table names, etc.).

Cheers,
Vlad


Hello. Pardon me for jumping in but storing duplicate information in multiple tables is usually not recommended. Wouldn’t it be sufficient to simply add a status field to the same table to indicate if the record is a purchase request or an order? In some cases, using an Order Date field is enough.
 

Attachments

  • William - Copy.zip
    314.4 KB · Views: 40

bastanu

AWF VIP
Local time
Today, 14:08
Joined
Apr 13, 2010
Messages
1,174
William, have a look at the attached db, I have removed some fields that were not really needed in my view and remade your relationships. It seems to function well according to my limited testing. Why do you need two sets of fields in the tblLotPrticular (quantity/cost/total)? I don't usually store calculated totals in calculated fields as you can always do in a query or report when you need it. Also I would recommend replacing the Field1 with attachment data type in tblEndUser with a table in which you would have three (or four) fields:
AttachmentID (autonumber,PK),EndUserID(Number,long,FK),AttachmentFullName(text,full path and name to network location used to store them),AttachmentNotes(text, or memo, description of file, comments, etc.).

Cheers,
Vlad
 

Attachments

  • William -Vlad.zip
    136.6 KB · Views: 41
Last edited:

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
William, have a look at the attached db, I have removed some fields that were not really needed in my view and remade your relationships. It seems to function well according to my limited testing. Why do you need two sets of fields in the tblLotPrticular (quantity/cost/total)? I don't usually store calculated totals in calculated fields as you can always do in a query or report when you need it. Also I would recommend replacing the Field1 with attachment data type in tblEndUser with a table in which you would have three (or four) fields:
AttachmentID (autonumber,PK),EndUserID(Number,long,FK),AttachmentFullName(text,full path and name to network location used to store them),AttachmentNotes(text, or memo, description of file, comments, etc.).

Cheers,
Vlad

It is because in the Purchase Order, cost and sometimes the quantity is changed... They are tend to be changed because Purchase request values are all just estimates, unlike in purchase order the cost is the correct amount...

Thank you for this... I will try again to analyse this and apply in my BD...

May I ask additional help? How about in the POQuantity field, the calculation will only activate in the afterchange in the LotID combobox? is it possible, my intended calculation will be Quantity is equal to POQuantity, and make it editable? is it possible?

I am Very thankful on your help...
 

bastanu

AWF VIP
Local time
Today, 14:08
Joined
Apr 13, 2010
Messages
1,174
Have a look at the attached, I have added code in the after update events of quantity and cost in the purchase request lot subform to copy over the their values to the corresponding "po" fields if those are empty. SO when the items are added it will make the two sets of fields equal but you will be able to edit the PO ones in the purchase order form.

Cheers,
Vlad
 

Attachments

  • William -Vlad_Feb_13.zip
    171.9 KB · Views: 33

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
Have a look at the attached, I have added code in the after update events of quantity and cost in the purchase request lot subform to copy over the their values to the corresponding "po" fields if those are empty. SO when the items are added it will make the two sets of fields equal but you will be able to edit the PO ones in the purchase order form.

Cheers,
Vlad

Thank you sir..

The way it works is only when I am entering data in Request...
 

bastanu

AWF VIP
Local time
Today, 14:08
Joined
Apr 13, 2010
Messages
1,174
Not sure I understand what you mean. Isn't that the normal progression? Tentative values entered at the purchase request stage followed by final values entered in the actual purchase order? If you need something else please explain in more detail with actual form/control names and better screen shots with data entry and desired outcomes.

Cheers,
Vlad
 

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
Hope I am not misunderstood, a while ago I was searching of doing the Do Until and loop as an afterupdate in the LotID combobox, where when I select from LotID, the TblLotParticular subform1 will do a lookup in the TblLotParticular table LotID as the criteria and it will be displayed in the subform and auto valueing the POQuantity field... Then I read your reply and realized how wrong I was in my research... This procedure is correct...

This is ok sir... Its true that it is the normal progression of the work. This procedure is very helpful...

Thank you so much

hehehehe...
 

willsnake

Registered User.
Local time
Tomorrow, 05:08
Joined
Dec 3, 2018
Messages
52
There seems to be a problem sir... the code did not work on my last Purchase Request

on the REquest 19-02-010, the POQuantity runs 0
 

Attachments

  • william3.zip
    242.7 KB · Views: 32

bastanu

AWF VIP
Local time
Today, 14:08
Joined
Apr 13, 2010
Messages
1,174
Sorry William, I assumed the fields would be empty but you have them defaulted as 0 so please use this kind of code:
Code:
Private Sub Quantity_AfterUpdate()
If Nz(Me.POQuantity, 0) = 0 Then Me.POQuantity = Me.Quantity
End Sub
Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom