Multiple Text Box and Lookups

Question

I am currently trying to improve on an Access Database VBA that I have inherited from my predecessor at work. I have come unstuck on a particular form.

I have a form that at the moment is just a large form containing 32 individual textbox, with the same code behind each but it is the same code repeated for each textbox with just the references to the text box changing in each.

Private Sub Cand_No2_AfterUpdate()
Cand_Name2 = DLookup("[Name]", "[qryExamAbsences]", "[Cand_No] = Cand_No2")
End Sub

Then once the button is pressed

If Not IsNull([Cand_Name1]) Then
 Rope = Rope & " Or Cand_No = " & [Cand_No1]
End If

(The If statement is contained in the button mousedown event.)

Occurs for each text box which then filters a report that is printed for office use. There are many problems with this but the major one I am trying to solve is that there is an upper limit to the number of entries, if I need to filter more than 32 I would need to delete the text and start again as it were.

Is there a way of combining all this into a single section of code which will create text boxes when needed?

EDIT.

I have found a way to give the impression to the user that the text boxes are being created after each entry which has improved the form from a user standpoint (no longer having 32 textboxes or having to scroll down to the Print Button.) however this still hasn't solved the issue of messy code as I have had to repeat the extra code for each box again, it also leaves me with the maximum of 32 entries still.

The new code is as follows:

If Not IsNull(Cand_Name1.value) Then
 Cand_No2.Visible = True
 Cand_Name2.Visible = True
 cmdPrint.Top = 2500
 cmdPrint.Left = 2500
 DoCmd.MoveSize 1440, 2201, , 4000
Else
 Cand_No2.Visible = False
 Cand_Name2.Visible = False
 cmdPrint.Top = 2000
 DoCmd.MoveSize 1440, 2201, , 3500
End If

Essentially makes the next text box visible and moves the print button down to make room for the new text boxes. It also expands the window.

I'm a little confused as to what you're trying to do here with your code. You're putting the name of the matching Cand_No into a variable when the user is done typing in the text box, then using a button to add the numbers one at a time to a query string which is later used to print results? If we knew more about the end result and the inputs, there may be a much more efficient way to help you. — StuckAtWork
Sorry didnt realise I had missed some stuff that would be helpful. I'm not terribly skilled at this sort of thing yet just been lumbered with the task. The process is as follows, The User enters CAND_NO into a text box, this is then used to look up the cand_name and filter the report that is printed so that only those that are entered appear. The reason CAND_No is used is due to another process and the CAND_Name lookup still needs to occur so the user can make sure that the correct person is entered. The CAND_Name gets displayed in a text box next to the one that the user enters CAND_NO in — KorgyBoy
My aim is too remove some of the awkardness to the form. At the moment all 32 text boxes are visible and it just makes it look a mess, I also want to improve the code behind the form and just generally make it more efficient. I have inherited a database that has had 10 years of badly designed add ons and they dont want to move away from it so I am stuck trying to improve it instead. — KorgyBoy
could you convert the list to a multi-select list box? you could then check each line to see if it is selected, and construct the Rope variable from there — SeanC
That would work but the list box would have to include every possible CAND_No for that time period which would mean possibly 1000s of lines in the list box. — KorgyBoy

Zaider Zaider · Accepted Answer · 2012-05-29T16:43:23

Could you not just have 2 text boxes, one for CAND_NO and another for CAND_NAME and then beside those two boxes place an ADD CAND_NO button.

Create a list box that would list every CAND_NO / CAND_NAME after they press the add button so they can see what they've added so far. Then loop through your list box to build your rope string or have your rope string either a global variable on the form and build it as they add numbers or stored in a hidden text box storing the value as they add numbers if you don't like global.

Multiple Text Box and Lookups

1 Answers