VBA Row Source Code? (1 Viewer)

[steve21nj]

Good morning,

I am running into a problem with a form to form operation. I have a simple purchase order data entry form and subform that links to two tables. I am able to enter data and save with no problems. On this form, I have a link to another form (DHS1501) that is a pdf image replica of what we use at the office. In the image, I have various text boxes that are bound to a query. The query pulls in the information correctly until I reach the line items, 1-10. It will only copy the first line item and put the exact same line item in number 1 into the 2-10 fields. Basically it will not move to row 2 of the data.

I am not using a report because there are fields on this form that may need to get edited before printing. Is there a vba row source code to move to row 2-10?

Attached is my database, I am hoping someone can point me in the right direction.

Open the database, click (Add Purchase Order).
After the form opens, at the bottom, click Generate DHS 1501, and sample data will be in the form.

The information carries from the previous form, but as you will see in line item 2, it is a duplicate value of line item 1. It will not move to row (2) for the next line item.

Thanks in advance.

 

[Addyman]

OK, I see your issue (I think), if you open up the DHS1501 form after going through your instructions above, I see the same item duplicated, but then if I got record number 2, I see your second item (also duplicated).

Without spending a lot of time looking at it or thinking about it, I would maybe suggest using a subform for the Items (if possible). That way you can link using your requisition_id, set the subform to be a continuous form, and it should work.

 

[steve21nj]

I looked at that, but it would ruin the form integrity. I was courious if there was vba code that i could link each unbound field to the query such as:
Private Sub btnDHS_Click()
On Error GoTo Err_btnDHS_Click
Dim strDocName As String
strDocName = "DHS1501"
DoCmd.OpenForm strDocName
With Forms(strDocName)
.Caption = "Caption set from AddPurchaseOrder"
.txtItem1 = Me. ItemDescrip row(1)
.textItem2=Me.ItemDescrip row(2)
End With
The code would then identify if row 3-10 are not needed without error. I know the row (1) is not the proper code, but it was to get something going.
Any thoughts?

 

[boblarson]

I'm not seeing how you expect to get the data filled in correctly. The only way you can do this is to use code to set the values in unbound controls and iterate through the recordset like:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
 
Set db = CurrentDb
 
Set rst = db.OpenRecordset("tblLineItemDetails")
i = 1
If rst.RecordCount > 0 Then
   Do Until rst.EOF
     Me.Controls(i & "txtItem1") = rst("ItemDescrip")
     Me.Controls(i & "txtItem2") = rst("StockNum")
     ' ...etcetera...
   rst.MoveNext
   Loop
End If
 
rst.Close
Set rst = Nothing

 

[steve21nj]

Thank you for the feedback. When I put the code in the button to Generate DHS, I get a run-time ‘2465’ Microsoft Access can’t find the field ‘txtItem1’ referred to in your expression.
I have the following, just to finish out two lines. I am still in the learning phases of the proper uses of vba.

Option Compare Database

[SIZE=3][FONT=Calibri]Private Sub btnDHS_Click()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim db As DAO.Database[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim rst As DAO.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim i As Integer [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set db = CurrentDb [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rst = db.OpenRecordset("tblLineItemDetails")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]i = 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]If rst.RecordCount > 0 Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  Do Until rst.EOF[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("txtItem1") = rst("ItemDescrip")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("txtItem2") = rst("StockNum")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("texItem3") = rst("Quantity")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("texItem4") = rst("UnitofIssue")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("texItem5") = rst("UnitPrice")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("texItem6") = rst("Subtotals")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("2txtItem1") = rst("ItemDescrip")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("2txtItem2") = rst("StockNum")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("2txtItem3") = rst("Quantity")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("2texItem4") = rst("UnitofIssue")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("2texItem5") = rst("UnitPrice")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls("2texItem6") = rst("Subtotals")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  rst.MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  Loop[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]rst.Close[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rst = Nothing[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]End Sub  [/FONT][/SIZE]

I am either putting the code in the wrong place or not defining the form DHS1501.

 

[boblarson]

You didn't do it like I wrote it. You are mistaking that you can set more than one line at a time. You CAN'T. You have to use the naming with the code I gave you where you use the i (Integer) to dynamically put the data in the correct box. So

Me.Controls(i & "txtItem1") = rst("ItemDescrip")
Me.Controls(i & "txtItem2") = rst("StockNum")

and that would yield in essence:

Me.Controls("1txtItem1") = rst("ItemDescrip")
Me.Controls("1txtItem2") = rst("StockNum")

and then on the next record

(oh, I forgot to include the

i = i + 1

just before the rst.MoveNext part.)

And then on the second pass (for the second record) it would in essence yield:

MeControls(2txtItem1) = rst("ItemDescrip")
Me.Controls(2txtItem2) = rst("StockNum")

and so on.

 

[steve21nj]

I thought that’s what you wanted but I ran into a run time error which is why I started adding those other un-needed lines. The error I encounter is the Run-Time Error ‘2465’. “Microsoft Access can’t find the field ‘1txtItem1’ referred to in your expression. I am experience the same error with the code below.
I have the code as you suggested. I have the code for the on click command of my entry form that brings me to the DHS form.

[FONT=Calibri][SIZE=3]Private Sub btnDHS_Click()[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Dim db As DAO.Database[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim rst As DAO.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim i As Integer[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]Set db = CurrentDb[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]Set rst = db.OpenRecordset("tblLineItemDetails")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]i = 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]If rst.RecordCount > 0 Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  Do Until rst.EOF[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls(i & "txtItem1") = rst("ItemDescrip")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls(i & "txtItem2") = rst("StockNum")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls(i & "texItem3") = rst("Quantity")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls(i & "texItem4") = rst("UnitofIssue")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls(i & "texItem5") = rst("UnitPrice")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Me.Controls(i & "texItem6") = rst("Subtotals")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   i = i + 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  rst.MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  Loop[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]rst.Close[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rst = Nothing[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

 

[boblarson]

You need to have the code AFTER the DHS form comes up. So you need to have the code to open it first, before this other code part.

 

[steve21nj]

It still pulls the same error. Before I sent the message I tried putting:

Dim stDocName As String
 Dim stLinkCriteria As String
        stDocName = "DHS1501"
        DoCmd.OpenForm stDocName, , , stLinkCriteria

right before Dim db As DAO.Database.....

I know your hating me right now, but I'm learning alot.

 

[boblarson]

You aren't using stLinkCriteria, so get rid of it.

Dim stDocName As String
   stDocName = "DHS1501"
   DoCmd.OpenForm stDocName

And then make sure you have changed the controls (1txtItem1, 2txtItem1, etc.) back to UNBOUND controls.

 

[steve21nj]

Boblarson,
I really appreciate your help, unfortunately, I am experiencing the same error 2465. I have done exactly as you have suggested with no luck. I have attached my database again to see if you can point out something different that I can’t see.

 

[boblarson]

Well, for one, your first line you have your text boxes without the 1 in front of them. So, across line one you have

txtItem1, txtItem2, txtItem3, txtItem4, txtItem5, txtItem6, txtItem7

When they SHOULD be named:

1txtItem1, 1txtItem2, 1txtItem3, 1txtItem4, 1txtItem5, 1txtItem6, 1txtItem7


Also, your code had 3texItem1 (instead of 3txtItem1). And we were using ME. instead of the correct form reference.

So here's the fixed code (you just need to fix the names of the first line text boxes).

Private Sub btnDHS_Click()
 Dim stDocName As String
        stDocName = "DHS1501"
        DoCmd.OpenForm stDocName
        
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
 
Set db = CurrentDb
 
Set rst = db.OpenRecordset("tblLineItemDetails")
i = 1
If rst.RecordCount > 0 Then
   Do Until rst.EOF
     Forms!DHS1501.Controls(i & "txtItem1") = rst("ItemDescrip")
     Forms!DHS1501.Controls(i & "txtItem2") = rst("StockNum")
     Forms!DHS1501.Controls(i & "txtItem3") = rst("Quantity")
     Forms!DHS1501.Controls(i & "txtItem4") = rst("UnitofIssue")
     Forms!DHS1501.Controls(i & "txtItem5") = rst("UnitPrice")
     Forms!DHS1501.Controls(i & "txtItem6") = rst("Subtotals")
    i = i + 1
   rst.MoveNext
   Loop
End If
 
rst.Close
Set rst = Nothing
End Sub

 

[steve21nj]

you....are....awesome! thank you so much. i've been pounding my head against the desk for a few days on this!

 

[steve21nj]

Bob,
I changed a line to the code because it would continue the loop, eventually producing an error ‘2464’ “can’t find the field 11txtItem1.”

Using the same example, with 10 line items, after I click on generate 1501, the bottom of the form will indicate that this is record 1 of 10, saying that each line item is a new record, which ultimately prints 10 pages of the exact same thing. If I were to add additional purchases, the number would increase and it begins to stop showing data properly.

So my question is how can I remove that to indicate record 1 of 1? Further, there may be times where the user might have to change a shipping address or another field. Is there a way to protect the form where they can edit without changing the table values? With what I have tried, it ends up printing a blank form or changes the entire table values.

This is the code I am currently using which returns a ‘2465’, MS Access can’t find the field ‘fk_RequisitionID’ referred to in your expression. The fk_RequisitionID is located on my subform "tblLineItemDetails subform"

Basically I want to click the 1501 command and only generate the info currently on the AddPurchaseOrder form.

Any thoughts?

[FONT=Arial][FONT=Arial]Private Sub btnDHS_Click()[/FONT]
[FONT=Arial]Dim stDocName As String[/FONT]
[FONT=Arial]      stDocName = "DHS1501"[/FONT]
[FONT=Arial]      DoCmd.OpenForm stDocName[/FONT]
 
[FONT=Arial]Dim db As DAO.Database[/FONT]
[FONT=Arial]Dim rst As DAO.Recordset[/FONT]
[FONT=Arial]Dim i As Integer[/FONT]
 
[FONT=Arial]Set db = CurrentDb[/FONT]
 
[FONT=Arial]Set rst = db.OpenRecordset("tblLineItemDetails")[/FONT]
 
[FONT=Arial]Me.Requery[/FONT]
[FONT=Arial]Me![fk_RequisitionID]![tbllineItemDetails subform].SetFocus[/FONT]
[FONT=Arial]DoCmd.GoToRecord acDataForm, "AddPurchaseOrder", acGoTo, fk_RequisitionID[/FONT]
 
[FONT=Arial]i = 1[/FONT]
[FONT=Arial]If rst.RecordCount > 0 Then[/FONT]
[FONT=Arial] Do Until i > 10[/FONT]
[FONT=Arial]   Forms!DHS1501.Controls(i & "txtItem1") = rst("ItemDescrip")[/FONT]
[FONT=Arial]   Forms!DHS1501.Controls(i & "txtItem2") = rst("StockNum")[/FONT]
[FONT=Arial]   Forms!DHS1501.Controls(i & "txtItem3") = rst("Quantity")[/FONT]
[FONT=Arial]   Forms!DHS1501.Controls(i & "txtItem4") = rst("UnitofIssue")[/FONT]
[FONT=Arial]   Forms!DHS1501.Controls(i & "txtItem5") = rst("UnitPrice")[/FONT]
[FONT=Arial]   Forms!DHS1501.Controls(i & "txtItem6") = rst("Quantity") * rst("UnitPrice")[/FONT]
[FONT=Arial]  i = i + 1[/FONT]
[FONT=Arial] rst.MoveNext[/FONT]
[FONT=Arial] Loop[/FONT]
[FONT=Arial]End If[/FONT]
 
[FONT=Arial]rst.Close[/FONT]
[FONT=Arial]Set rst = Nothing[/FONT]
 
[FONT=Arial]End Sub[/FONT]
[/FONT]