VBA Multiple Items to save against One Record. (1 Viewer)

Local time
Today, 10:01
Joined
Oct 7, 2016
Messages
43
I Have multiple Queries in Trans Form, but will ask one by one in each threads

I Have a three tables : Item_Mast, Flt_Master and Trns and all are linked as shown in Snapshot attached.

In Trans Form : All Three TABLES Data will be requiring in Trans form.
Here Combo Box: Item_Name will display and All the item from item_Mast table where MenuCategory='Food' and this item will save in trns table.
From item_Mast table where MenuCategory='SPML' and all the item_Name will save in Trans table along with Qty entered where Qty >0 in trns Table

Query : How to Create Sub Form/ Grid Format containing item_Mast!Item_Name where MenuCategory='SPML' , and Qty
should be saved as shown in Snapshot of Trans Table. ( Using VBA code)

Attached : (1) Snapshot (Showing Relationship Diagram Form: Item Master , Form : Trns, , Table: Trns)
(2) Unable to attach Ms Access database File, kindly allow the Access File to attach.
All.JPG
 

June7

AWF VIP
Local time
Today, 09:01
Joined
Mar 9, 2014
Messages
5,423
AFAIK, should be able to attach Access file. Try zipping with Windows Compression and attaching zip file.

It's not clear to me what you are asking for. Looks like you have form built so what is wrong? Why do you need code to save a QTY - why is this not input by user?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:01
Joined
May 7, 2009
Messages
19,169
AFAIK, should be able to attach Access file
previous post of this OP mentioned he is using SQL server.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:01
Joined
May 7, 2009
Messages
19,169
sample demo using msAccess tables.
 

Attachments

  • cabin_needs.accdb
    896 KB · Views: 334

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:01
Joined
Feb 19, 2002
Messages
42,971
1. Do NOT include an autonumber/identity column UNLESS it will be the primary key. That is the only use for this data type.
2. Although there is nothing technically wrong with having a multi-field PK, you will find that with Access, you will not be able to use that table in a list or combo box because it dosn't have a single-field unique, PK. Add an Autonumber to this table. Make it the PK and make a unique index for the other three fields.
3. The meal table allows duplicates within category. unless you have a compound unique index on category and name.
 
Local time
Today, 10:01
Joined
Oct 7, 2016
Messages
43
AFAIK, should be able to attach Access file. Try zipping with Windows Compression and attaching zip file.

It's not clear to me what you are asking for. Looks like you have form built so what is wrong? Why do you need code to save a QTY - why is this not input bSir

AFAIK, should be able to attach Access file. Try zipping with Windows Compression and attaching zip file.

It's not clear to me what you are asking for. Looks like you have form built so what is wrong? Why do you need code to save a QTY - why is this not input by user?
Thanks Sir, I am attaching the Access File in Zip Format, Requesting you to Please try to enter Qty Fields in Trns Form.
 

Attachments

  • trns.zip
    34.5 KB · Views: 289

June7

AWF VIP
Local time
Today, 09:01
Joined
Mar 9, 2014
Messages
5,423
Qty in the subform is UNBOUND so every record shows the same value. This is normal behavior. Item_Mast table does not have a Qty field so why do you have a Qty textbox in subform? Why do you have Item_Mast table in a subform?

Usual design with many-to-many relationship would be a main form bound to either Item_Mast or FLT_Master and subform bound to Trns with a combobox for whichever table is not bound to main form.

Using a single form bound to Trns means should be two comboboxes.

Took a closer look at data structure. Why is data repeated from FLT_Master and Item_Mast tables into Trns? Trns should not have fields for Flight_No, AirCraft_Type, Item_Name. Why have autonumber ID fields and not use them as primary key?

If you want multiple entries entered into Trns, then do data entry. If you want to automate creating multiple records, that will require VBA.

There is no need to even define PK in Trns unless that key must be saved as foreign key into a related table. You can set the fields as a compound index to prevent duplicate pairs. However, since Trns PK is not saved as foreign key into another table, having compound PK should not create issue but don't include autonumber TRNS_ID in key or index if you want to prevent duplicate pairs of Flight_ID and Item_ID.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:01
Joined
May 7, 2009
Messages
19,169
see this demo in A2007.
 

Attachments

  • cabin_needs_A2007.accdb
    768 KB · Views: 276
Local time
Today, 10:01
Joined
Oct 7, 2016
Messages
43
see this demo in A2007.
Thanks a lot Sir for the attached File. It solved my issues,
but as stated above I have Many Queries, Requesting you to please solve my the below mentioned query :-

Table : tblFlight_Detail is added.
tblFlight is linked with tblFlight_Detail (One to Many Relationship) ( Access File attached )

I need to added Transaction Form to Linked with Sub form with Quantity need to enter by user ( As Show in attached Pic)\
Kindly Suggest.

28_Jun_Trns_flight_Detail.JPG
28_Jun_Relationship.JPG
 

Attachments

  • cabin_needs_A2007.accdb
    1.3 MB · Views: 292

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:01
Joined
May 7, 2009
Messages
19,169
is the Flight Sector, standard for all flights so we can add it in its own table?
 
Local time
Today, 10:01
Joined
Oct 7, 2016
Messages
43
is the Flight Sector, standard for all flights so we can add it in its own table?
No Sir, Flight Sector May vary

( Example : Single Sector Flights: DEL-FRA

( : double Sector Flights: DEL-FRA
Flights: FRA-DEL

( : Multiple Sector Flights: DEL-FRA
Flights: FRA-MUC
Flights: MUC-FRA
Flights: FRA-DEL )
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:01
Joined
May 7, 2009
Messages
19,169
here check and test.
 

Attachments

  • cabin_needs_A2007 (1).accdb
    736 KB · Views: 302

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:01
Joined
May 7, 2009
Messages
19,169
i thought a2010 will work on a2007.
 

Attachments

  • cabin_needs.accdb
    1.1 MB · Views: 181

Users who are viewing this thread

Top Bottom