VBA Multiple Items to save against One Record.

[anandan.cr@gmail.com]

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.

Kindly suggest

 

[June7]

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.

 

[anandan.cr@gmail.com]

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]

is the Trans_ID "continuous" through out the table.
or will it re-start (to 1) for each Flight Number?

 

[anandan.cr@gmail.com]

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]

try this:

 

[anandan.cr@gmail.com]

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]

i see. i did not anticipated that.
here is the change.

 

[anandan.cr@gmail.com]

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]

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.

 

[anandan.cr@gmail.com]

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)

 

[anandan.cr@gmail.com]

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]

edit ID of table dbo.Trns in MSSQL as Int only (don't make it as PK).

 

[anandan.cr@gmail.com]

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]

bring your in Design view.
Click on the subform.
on it's Property->Data:

Master Link Fields: Challan_No
Child Link Fields: Challan_No