By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,942 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

Adding multiple records to a subform

P: 3
Hi everyone!
I'm trying to add multiple records to a subform, based on the values, which I enter into the form. There is no possibility to do that using Macros and I'm a newbye in VBA.
The Form has two fields (in addition to ID): Name and Number.
The Subform also has two fields (in addition to ID and the Linked field): SubName and SubNumber.
The code should add N records to the Subform (N = Number from the form), insert the value from Name (Form) into SubName (in the Subform) and insert a number from 1 to N in the SubNumber.
Example:
Form
ID:1, Name:DocA, Number:3
Subform
ID:1 LinkedFIeld:1, Name:DocA, Number: 1
ID:2 LinkedFIeld:1, Name:DocA, Number: 2
ID:3 LinkedFIeld:1, Name:DocA, Number: 3

I have found a solution, which seems to help, but it returns errors. As far as I understand: it refers to the Recordset of the form, when it should refer to the Recordset of the Subform.

Private Sub Pages_AfterUpdate()
Dim rst As DAO.Recordset
Dim i As Long
Dim pgs As String
pgs = Forms![TB1]![Number]

Set rst = Me.Recordset
For i = 1 To pgs

rst.AddNew
rst("SubName").Value = "Name"
rst("Page").Value = i
rst.Update
Next i
Set rst = Nothing

End Sub


For you to understand the scope of this: I am trying to create a report, which whould print images (JPG scans of documents) based on their paths. There are documents, that have more than one page.
I'm using Form to add the Document and number is the number of pages in that document. I would like to be able to add the Document and the Number of pages and have the subform automatically populated with its pages. Basically, I will have a report, which will print path/DocA-1.jpg, path/DocA-2.jpg, path/DocA-3.jpg.

BTW: if you can help me figure out another solution to print the Documents in one report - I would be very obliged. Please keep in mind: most of the documents would have 1 page, but there are documents that have 2,3 and more pages.
Jul 14 '17 #1
Share this Question
Share on Google+
2 Replies


P: 3
A simplier way, would be to use Multiple Choices Dropdonw box. In this case, the query would return 1 line per each value. Lets say, the document has 3 pages and when the user selects pages: 1, 2 and 3 - the query would return three lines, with values 1, 2 and 3. That is enough to create an expression using the Path + DocName + PageNUmber.
For this case - is it possible, using VBA code, to automatically check options 1 and 2 (in Multichoice Dropdown), when 3 is checked by the user?
Jul 14 '17 #2

P: 3
Solved!

Option Compare Database

Private Sub Pages_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim i As Long
Dim pgs As String
Dim Superid As Long
Dim Supername As String


pgs = Forms![TB1]![Pages]
Superid = Forms![TB1]![T1-id]
Supername = Forms![TB1]![DocName]


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TB2", dbOpenTable)

For i = 1 To pgs

rst.AddNew
rst("SuperDoc").Value = Superid
rst("SubDocName").Value = Supername
rst("Page").Value = i
rst.Update
Next i
Set rst = Nothing

End Sub
Jul 14 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.