Advice on how to make a form that continually adds text boxes?

[Vita]

I get a set of lot numbers everyday that need to have a report printed for each lot.
EX: theres 10 lots. I need to print lot 1, 3, and 7 through the "lot report" which displays the quantity, location, etc about that specific lot.
So I would have a sheet for lot 1, a sheet for lot 3, and a sheet for lot 7.

I think I can handle the vba to passthrough the text from a textbox into a variable and open a report using that variable but my bigger issue is that everyday the amount and which lots I need change.
So monday could be 10 lots while tuesday could be 15.
I want to make a form that the user can input the Lot number in a textbox and either click a button to add another textbox or upon hitting enter it automatically creates a new textbox below the existing one.


I am thinking the best route for this is to set the enter key behavior to default (act like tab) and in the after update of the first textbox have it create another textbox with the same name but increase the number.
My problem is that the new textbox would not have this code inside the after update event so it would not be able to create a 3rd textbox.

Any advice/tutorials on doing something like this?

[pbaldy]

I might use a temp table and a continuous form bound to it. At the start, empty the table. Since it's now a bound form, as soon as the user enters a value a new textbox (record) will appear below. That gives you a table with your values, so you can join to that table to get the restricted records instead of needing code.

[Edgar]

pbaldy's suggestion is your best bet for your desired behavior.

Another thing you can do is to simply write your lot numbers separated by commas and make vba spit out the reports based on that string using the split function.

Another, even better approach would be to let the user select the lots from a multiselection listbox, assuming you have lot records somewhere.

[Vita]

I might use a temp table and a continuous form bound to it. At the start, empty the table. Since it's now a bound form, as soon as the user enters a value a new textbox (record) will appear below. That gives you a table with your values, so you can join to that table to get the restricted records instead of needing code.

I'm not sure what you mean by restricted records? I will give this a try though. Continuous forms still confuse me so I will have to do some research.

pbaldy's suggestion is your best bet for your desired behavior.

Another thing you can do is to simply write your lot numbers separated by commas and make vba spit out the reports based on that string using the split function.

Another, even better approach would be to let the user select the lots from a multiselection listbox, assuming you have lot records somewhere.

A multiselection listbox would be a great idea if I didn't have upwards of 23k records to select from! I like the idea!
CSV is another interesting method I might try if I can't get the continuous form idea to work.

Is there a way to just send a report to print instead of having to make it open, click print, and close it just to open another?
Something like (pseudocode)

DoCmd.PrintReport "Lots",,,"LotNumber = '" & (Variablefortextbox) & "'"

[pbaldy]

Restricted records means the lots that were input, not all lots.

If you look at the arguments of OpenReport, one determines if it previews or prints.

[Gicu]

Look up Docmd.OpenReport method, if you use acViewNormal the report gets printed immediately.
https://learn.microsoft.com/en-us/of...cmd.openreport
Cheers,

[Edgar]

A multiselection listbox would be a great idea if I didn't have upwards of 23k records to select from! I like the idea!
CSV is another interesting method I might try if I can't get the continuous form idea to work.

If you have many records then you also need to be able to search and still use the string method I mentioned, it's pretty simple really. Attached you will find 3 approaches:
1. the continuous form with temporary table approach, I don't really like it though
2. the string method with a list of lots as continuous form + search functionality (double click on lot_number to add lots)
3. the string method with a list of lots as listbox + search functionality (double click on row to add lots)

[Vita]

If you have many records then you also need to be able to search and still use the string method I mentioned, it's pretty simple really. Attached you will find 3 approaches:
1. the continuous form with temporary table approach, I don't really like it though
2. the string method with a list of lots as continuous form + search functionality (double click on lot_number to add lots)
3. the string method with a list of lots as listbox + search functionality (double click on row to add lots)

All of these methods are great! They like the continuous form with temp table so I am going through with that.

Now I have a different issue. (I was thinking about making a separate thread for it but it is relevant to this as well and I wasn't sure so I just added it to this thread.)

I have some fields I want them to be able to enter that aren't in the original lots table. Such as Quantity, length, etc.
So on the report I am trying to use a Dlookup as a control source but I am getting a type error even though both the ID and the Autonum are integers (Well technically the Autonum is an Autonumber but I am pretty sure int's can compare to an autonum without needing the CInt I included right?)

=DLookUp("Quantity","TempPullsTableTest",CInt([Autonum])=[TempPullsTableTest].[ID])
'or
=DLookUp("Quantity","TempPullsTableTest",CInt([Autonum])=[TempPullsTableTest]![ID])

Using this it prompts me to enter the value for TempPullsTableTest. No matter what I enter I get #Name? for using a '.' to reference the table and #Type! when I use '!'.
I am not sure whats wrong with this syntax?
The record source for the report is a query that pulls a bunch of fields from the Lots table so that it can fill a lot of controls on the report so I figured it would be easier to use a DLookup rather than figure out how to add the fields from TempPullsTableTest into the Query.

[Minty]

You have a field called Autonum ? And in the same table it should match a field called ID ?
Or is the Autonum field on the form ?

If it's on the form then simply use

=DLookUp("Quantity","TempPullsTableTest","[ID] = " & Me.Autonum)

Notice we are concatenating the field into the string

[Vita]

You have a field called Autonum ? And in the same table it should match a field called ID ?
Or is the Autonum field on the form ?

If it's on the form then simply use

=DLookUp("Quantity","TempPullsTableTest","[ID] = " & Me.Autonum)

Notice we are concatenating the field into the string

Two different tables. Lots table which has a field called Autonum and a TempPullsTableTest table that has ID.
I want the Quantity from TempPullsTableTest where the ID equals the Autonum that the report is using.

The record source of the report is set to the Lots table so I guess I am just trying to pull data from a second table but I am doing it inside of a control source.
(Sorry for the confusing names)

[Minty]

Why not just add the table in the underlying report query. it will be way more efficient.

[Vita]

Why not just add the table in the underlying report query. it will be way more efficient.

I just did that. I don't know why I didn't I was just being dumb.
I still don't understand why this DLookUp doesn't work though. It seems like it should be able to pull the data from that table.
It works now though thanks!

[Vita]

If you have many records then you also need to be able to search and still use the string method I mentioned, it's pretty simple really. Attached you will find 3 approaches:
1. the continuous form with temporary table approach, I don't really like it though
2. the string method with a list of lots as continuous form + search functionality (double click on lot_number to add lots)
3. the string method with a list of lots as listbox + search functionality (double click on row to add lots)

Using the TempTable option, I am having an odd issue. If the user decides to just enter 1 row and click the button then I get the following error.

Run-time error '2455':

You entered an expression that has an invalid reference to the property |. 'Atleast it looks like the pipe symbol

When I click debug it points me "Me.Dirty = False".
It only happens when having 1 line

[Edgar]

I could not reproduce the error. Can you try this?

Private Sub btnReport_Click()
'    Me.Dirty = False
    DoCmd.RunCommand acCmdSaveRecord
    Dim strSQL As String
    strSQL = "SELECT lot_number FROM temp_lots"
    DoCmd.OpenReport "rpLot", acViewReport, , "lot_number IN (" & strSQL & ")"
End Sub

[Vita]

I could not reproduce the error. Can you try this?

Private Sub btnReport_Click()
'    Me.Dirty = False
    DoCmd.RunCommand acCmdSaveRecord
    Dim strSQL As String
    strSQL = "SELECT lot_number FROM temp_lots"
    DoCmd.OpenReport "rpLot", acViewReport, , "lot_number IN (" & strSQL & ")"
End Sub

I tried this. It has the same issue but doing this along with a good nights sleep helped me realize it is because I have this code in the Form_AfterInsert event but since I am only entering one record and not going to the next line it technically isn't doing an insert yet.

Private Sub Form_AfterInsert()
    Me.[#txt] = DCount("Autonum", "TempPullsTableTest")
End Sub

#txt is the name for the textbox. Didn't know what to call that since its just a number.


The point of this code is to have a number next to the lot.
These come daily and we number them in order for traceability and to make double checking we got the right lots easier.
I was attempting to use DCount on the autonum to have that number proceed in order while also being able to go back and edit a line incase of typo.
EX:

#1 Lot:1552 quantity:20
#2 Lot:567   quantity:50
#3 Lot:782   quantity:70

I am going to try and figure out what event to put this code in. Thanks! If anyone has any suggestions for this, that is appreciated too.
Maybe some way to count the amount of rows in the continuous form?