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

Gasman

Enthusiastic Amateur
Local time
Today, 15:35
Joined
Sep 21, 2011
Messages
14,056
i thought a2010 will work on a2007.
No, I constantly am unable to open your DBs and have to ask for a 2007 version if I am really keen to see how it was done? :)
 
Local time
Today, 08:35
Joined
Oct 7, 2016
Messages
43
Dear Arnelgp,

In below mentioned VBA Code :

CurrentDb.Execute "insert into tblTrans ( FlightID, ItmID ) " & _
"select " & Me.Parent!FlightID & ", ID FROM tblItems WHERE [Category] = '" & strCategory & "'"



On bulk Insert records, How to use getNextItemNum() , for the Field
TRNS_ID: is Auto Increment using getNextItemNum()


As per Attached Image and Ms Access file.

getNextItemNum.JPG


Kindly suggest
 

Attachments

  • cabin_VER_1_.accdb
    2.4 MB · Views: 91

June7

AWF VIP
Local time
Today, 07:35
Joined
Mar 9, 2014
Messages
5,425
There is no Trns_ID field in any table. Exactly what do you want to do - assign a sequence number to each transaction under each flight? getNextItemNum() cannot be used for that. Review http://allenbrowne.com/ranking.html

Access 2007 should be able to read Access 2010 file if there are no Access 2010 specific features used.
 
Local time
Today, 08:35
Joined
Oct 7, 2016
Messages
43
There is no Trns_ID field in any table. Exactly what do you want to do - assign a sequence number to each transaction under each flight? getNextItemNum() cannot be used for that. Review http://allenbrowne.com/ranking.html

Access 2007 should be able to read Access 2010 file if there are no Access 2010 specific features used.


Trns_ID field is in Trans Table as Primary Key. It is an ACCB File Linked with Ms SQL. For easy Work i uploaded mdb file.
Trns_ID got Increment using getNextItemNum() . I have not used sequence or Auto Increment for Increment value, just used the function.

When entering single record, Trns_ID got increment , but when I enter Bulk records It did not works.

Syntax is :-

Docmd.RunSql "insert into tblTrans ( Trns_ID ,FlightID, ItmID ) " & _
"select " & getNextItemNum() & ", FlightID, ID FROM tblItems WHERE [Category] = '" & strCategory & "'"



Kindly correct the above VBA syntax..
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:35
Joined
May 7, 2009
Messages
19,175
is the Trans_ID "continuous" through out the table.
or will it re-start (to 1) for each Flight Number?
 
Local time
Today, 08:35
Joined
Oct 7, 2016
Messages
43
is the Trans_ID "continuous" through out the table.
or will it re-start (to 1) for each Flight Number?
Trans_ID is increment field as per record added (VBA Code is written for increment)
It is Primiary Key ( Can't Reset)

Kindly suggest for Bulk Record insert using Function getNextItemNum()
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:35
Joined
May 7, 2009
Messages
19,175
try this:
 

Attachments

  • cabin_needs.accdb
    1.6 MB · Views: 102
Local time
Today, 08:35
Joined
Oct 7, 2016
Messages
43
try this:


Arnelgp you are Generous, I appreciate that you understanding the problems and writing a perfect Code.

Reference you attached Ms Access File : I have one small query if use Multiple time select and drop item in combo box for eg "SPML" the Trns_ID got Increment every time.

Pls stop it for existing Items , It should work only new records.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:35
Joined
May 7, 2009
Messages
19,175
i see. i did not anticipated that.
here is the change.
 

Attachments

  • cabin_needs.accdb
    832 KB · Views: 98
Local time
Today, 08:35
Joined
Oct 7, 2016
Messages
43
i see. i did not anticipated that.
here is the change.

Trns_Id is a Key Field (Foreign Key ) , After I changed it to Foreign Key .
and Trying to enter data in Transaction Form, VBA program is giving an Error at rs.movefirst

Arnelgp, Kindly suggest.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:35
Joined
May 7, 2009
Messages
19,175
foreign key in SQL server.
can you remove it as foreign key (foreign key to what table/field?).
this number is generated by ms Access.
can you just test it as Int.
 
Local time
Today, 08:35
Joined
Oct 7, 2016
Messages
43
foreign key in SQL server.
can you remove it as foreign key (foreign key to what table/field?).
this number is generated by ms Access.
can you just test it as Int.

As discussed above Trns_ID field here it is dbo_trns!id

My Real life Scenario : MsSQL (Back End ) and Front End is (Ms Access)
below mention is a Trns Form ( Delivery Order Challan).

Main Form ( Invoice_Mast ),
Invoice_Mast!Challan_No (Primary Key)

Sub-form ( dbo_Trns)
dbo_Trns!id is Primary Key (Using Code Auto Increment)
dbo_Trns!Challan_No (Foreign Key)


Trns_Form.JPG
 

Attachments

  • Relation_digrma.JPG
    Relation_digrma.JPG
    50.3 KB · Views: 72
Local time
Today, 08:35
Joined
Oct 7, 2016
Messages
43
As discussed above Trns_ID field here it is dbo_trns!id

My Real life Scenario : MsSQL (Back End ) and Front End is (Ms Access)
below mention is a Trns Form ( Delivery Order Challan).

Main Form ( Invoice_Mast ),
Invoice_Mast!Challan_No (Primary Key)

Sub-form ( dbo_Trns)
dbo_Trns!id is Primary Key (Using Code Auto Increment)
dbo_Trns!Challan_No (Foreign Key)


View attachment 93133


arnelgp , Please help me.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:35
Joined
May 7, 2009
Messages
19,175
edit ID of table dbo.Trns in MSSQL as Int only (don't make it as PK).
 
Local time
Today, 08:35
Joined
Oct 7, 2016
Messages
43
edit ID of table dbo.Trns in MSSQL as Int only (don't make it as PK).

In Ms SQL FK is removed from field dbo.Trns@id , but In MsAccess while creating Main Form and Sub Form
between Table: dbo_Invoice_Mast and Table:dbo_Trns, Table Relation is must thus I was forced to link as One to Many Relationship.
using Challan_No


Table: dbo_Invoice_Mast!Challan_No and Table:dbo_Trns!Challan_No
One to Many Relationship.

Please Suggest further
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:35
Joined
May 7, 2009
Messages
19,175
bring your in Design view.
Click on the subform.
on it's Property->Data:

Master Link Fields: Challan_No
Child Link Fields: Challan_No
 

Users who are viewing this thread

Top Bottom