Check textbox array for null values

[tt1611]

Hey
Is it possible to check for the following
My form has 7 sets of textboxes on 7 different lines
so
line 1 has id1, device1, serial1, model1...up to 7th box
line 2 has id2, device2, serial2, model2...up to 7th box
I have some values to input into these box sets automatically but I need a way for access to check and see which set is null to enter information into. The trick is, if set1 is not null then the code should check set 2 to see if null and so on and so forth till it finds an empty set to input data into.
There will always be at least one set of boxes available to input this captured information into.
I have been toying with the idea of declaring 7 different arrays each with 7 elements along the lines of.

dim txt1find(7) as textbox
txt1find(1) = id1
txt1find(2) = device1
txt1find(3) = serial1...

then declare another array txt2find(7)

dim txt2find(7) as textbox
txt2find(1) = id2
txt2find(2) = device2
dim txt3find(7) as textbox

'and so on till I get to

dim txt7find(7) as textbox

My first question is does this seem tedious or can anyone suggest an easy way to get access to check which array contains null values so I can run my code to populate those textboxes when it finds the empty set, else it should skip to the next set till it finds one empty?
Please let me know if this needs more clarification.

 

[Galaxiom]

Sounds very much like the real problem is denormalized data.

If not the data then certainly the mode of presentation. Have you considered Continuous Forms? You would only need one set of combos and you can have a many rows as you need without complcations.

 

[tt1611]

Hey
The form is normalized as well as the data behind the form (which is reading of a view from 7 tables)

The users can input as many as 7 different devices onto this form which in turn generates a paper based version of their entries. The idea is for a user to search for a specific device and all serial details for that device are exported to my form in question.

This second form then uses formfields to populate the MS Word document. Without boring you with all the details is it actually possible to check an array has null elements?

 

[CBrighton]

7 tables with the same fields to hold data on 7 similar products sounds like the opposite of normalised to me!

A single table could hold this, just add an 8th field for "DeviceType".

 

[Galaxiom]

Confirmed. Profoundly denormalized data.

 

[tt1611]

Thank you all..I'm trying to push away from the issues of data normalization and denormalization for a sec and just ask that given the above scenario is it possible to check a group of textboxes in an array for null values

 

[CBrighton]

The thing is, if you normalise it and change it to a continuous form then you will only have 7 controls and there won't be up to 6 sets of controls with no data in them as it only adds another record (and therefore another set of controls)when the user stars populating it with data.

Your problem is caused by the data denormalisation, creating a fix for it until you normalise the data just means it will take longer to fix.

 

[Galaxiom]

Normalization is not an aesthetic whim of database puritans but the very foundation principle of database design. Persuit of patches to overcome the superficial consequences is a waste of effort.

 

[tt1611]

Thanks Brighton
OK so some history lessons while we are at it. I joined this department while they were still working with scrap notes and xls files trying to account for 600+ PCs and a whole bunch of devices connected to each PC eg Printers, Monitors, Scanners, Cash Draws etc etc.

The decision was made to keep each device in a separate table for easier management. The parent table has always been the PC and each device has an FK plugged into to it with the PC name so in a nutshell a device can either be in storage in which case the FK field is null or in play in which case the FK field has the PC name (if it exists). We are still trying to tie up what PC has what device (Inventory is round the corner if you get my drift) and the current design has worked flawlessly. I accept the option of merging all child devices into one table and keeping the PC table separate was discussed but we then went with everyone having their own basket for easier additions and management of cost. Looking back now, all this can be accomplished with simple views but it means I would literally have to rewrite queries for a year old app and basically start it all from scratch.

The app has a number of functionalities from Asset management to a helpdesk ticketing system.
A new function I am trying to come up with now is the creation of this form that allws me to search for a PC and return a value if all connected devices have been signed for or return device not found naturally if it cant find it. These results then get sent to another form where on the user clicking generate report, a word document prints out that they can use to sign etc etc. So I have 7 tables (ie child devices) that can belong to one PC that may or may not have been signed for hence null values being a possibility in my form.

See where I'm going?

 

[tt1611]

Normalization is not an aesthetic whim of database puritans but the very foundation principle of database design. Persuit of patches to overcome the superficial consequences is a waste of effort.

Nice thought and the mere reason we all spend years trying to normalize every crunch of our tables...just a thought..I attest to the fact that although necessary, real life acceptance must also come into play...like software testing, no database is truly ever 100% normalized is it?

 

[Galaxiom]

Nice thought and the mere reason we all spend years trying to normalize every crunch of our tables

Not at all. As you are experiencing right now, it takes far more time to repeatedly work around poor normalization. Good data design is easy to interact with.

...just a thought..I attest to the fact that although necessary, real life acceptance must also come into play...like software testing, no database is truly ever 100% normalized is it?

Depends who developed it.

 

[stopher]

If I understand the OP correctly, we have a table for the PC and then a table for each of the attached devices. If this is the case then I do not see this as denormalised. Which normalisation rule does it break?

Instead I see it as a design decision that was taken on the basis that this information about a connected device is significantly different the the information about another connected device (or even the PC). It's a design decision that has pros and cons.

If I only plan to store Make, Model, Function, Serial, then a single tables sounds like a good plan. But if I'm storing screen resolution for monitors, and PC specs for PCs then clearly a single table approach is going to be challenging.

What I don't understand is what the OP is trying to do. If it's about showing several sub-tables against a main table, then why not just have a sub form for each connected device.

Where I do agree with the other contributors is that what do you do when another device is invented. This is one of the cons of your design choice.

Chris

 

[tt1611]

Hey Chris
In fact that is the exact route my design took. I have several subforms that open in tabbed pages where if a user clicks on a PC, corresponding data about that PC ie what individual device is connected to it is displayed on each tab ie Monitor, Printer and so on.

What I don't understand is what the OP is trying to do. If it's about showing several sub-tables against a main table, then why not just have a sub form for each connected device.

As mentioned, the above has been completed. Creating another device then although very far fetched would mean the creation of another table to store that device. However, this is highly unlikely. My tables do have some similar columns but also specific information can be stored about a particular device as well.

I guess the only way to really explain what I am needing is the creation of a sample database. I will see if I can model the forms and tables in question.

I yesterday developed code that goes to each of the devices and pulls the serials like so.

Private Sub txtname_DblClick(Cancel As Integer)
'PC Search
Select Case Me.txtPC
Case "3161 Needed"
Dim strSQLPC As String
Dim strPC As String
Dim rst As DAO.Recordset
strSQLPC = "SELECT Serial FROM PCS WHERE PCs.Device_Name = '" & txtname & "'"
Set rst = CurrentDb.OpenRecordset(strSQLPC)
strPC = rst!Serial
End Select
 
'Monitor Search
Select Case Me.txtMon
Case "3161 Needed"
Dim strSQLM As String
Dim strMon As String
Dim rstM As DAO.Recordset
strSQLM = "SELECT Serial_N FROM Monitors WHERE Monitors.PC_Name = '" & txtname & "'"
Set rstM = CurrentDb.OpenRecordset(strSQLM)
strMon = rstM!Serial_N
End Select
 
'Receipt Printer Search
Select Case Me.txtRec
Case "3161 Needed"
Dim strSQLRec As String
Dim strRec As String
Dim rstRC As DAO.Recordset
strSQLRec = "SELECT Serial_N FROM RPrinters WHERE RPrinters.Device_Name = '" & txtname & "'"
Set rstRC = CurrentDb.OpenRecordset(strSQLRec)
strRec = rstRC!Serial_N
End Select
 
'Cash Draw Search
Select Case Me.txtCashD
Case "3161 Needed"
Dim strSQLCashD As String
Dim strCashD As String
Dim rstCashD As DAO.Recordset
strSQLCashD = "SELECT Serial_N FROM cashdraws WHERE cashdraws.Device_Name = '" & txtname & "'"
Set rstCashD = CurrentDb.OpenRecordset(strSQLCashD)
strCashD = rstCashD!Serial_N
End Select
 
'Change Display Search
Select Case Me.txtCD
Case "3161 Needed"
Dim strSQLCD As String
Dim strCD As String
Dim rstCD As DAO.Recordset
strSQLCD = "SELECT Serial_N FROM cdisplay WHERE cdisplay.Device_Name = '" & txtname & "'"
Set rstCD = CurrentDb.OpenRecordset(strSQLCD)
strCD = rstCD!Serial_N
End Select
 
'ID Scanner Search
Select Case Me.txtIDScan
Case "3161 Needed"
Dim strSQLID As String
Dim strID As String
Dim rstID As DAO.Recordset
strSQLID = "SELECT Serial_N FROM idscans WHERE idscans.Device_Name = '" & txtname & "'"
Set rstID = CurrentDb.OpenRecordset(strSQLID)
strID = rstID!Serial_N
End Select
 
'CPS Search
Select Case Me.txtCPS
Case "3161 Needed"
Dim strSQLCPS As String
Dim strCPS As String
Dim rstCPS As DAO.Recordset
strSQLCPS = "SELECT Serial_N FROM CPS WHERE cps.pc_Name = '" & txtname & "'"
Set rstCPS = CurrentDb.OpenRecordset(strSQLCPS)
strSQLCPS = rstCPS!Serial_N
End Select
End Sub

My problem now is once I have extracted these serials, they should go on another form. That form CANNOT have blank lines in it and as can be seen in the code, the case of 3161 Needed being true is not always the case which means at the end of the run you could 5 lines out of 7 = 2 empty lines in the form the data is transported to..

I need to be able to check the newly opened form for any blank lines and have the above data populate to those lines as supposed to just writing on the first line it sees.

I hope this clarifies..

 

[CBrighton]

I'm still not sure exactly what you want to do, but you can check if a control is blank and take action based on that like this:

If isnull(txtControl) then
   'Control is blank
Else
   'Control has data
End if

 

[tt1611]

I'm still not sure exactly what you want to do, but you can check if a control is blank and take action based on that like this:

If isnull(txtControl) then
   'Control is blank
Else
   'Control has data
End if

This would have been fine Chris but the new form these serials are going has 7 textboxes (along with other controls). The idea is that each extracted serial should fit into each textboxes.
Serial 1 = textbox1
serial2 = textbox2 and so on

All well and dandy except if serial 1 is null then it means textbox 1 remains null which is a no no. I wanted in the case where serial 1 is null, when serial 2 came to write data it checks if tetxbox 1 is empty and if it is write to that line. This has to be the same principle for 7 other serials where 6 out of the 7 could be null if the select argument fails. In this case I dont want the serial writing to textbox 7 but rather to textbox 1.

 

[CBrighton]

So, something along these lines:

if isnull(txt1) then
   if isnull(txt2) then
      if isnull(txt3) then
         'input into txt3
      End if
   Else
      'input into txt2
   End if
Else
   'input into txt1
End if

 

[tt1611]

Chris i appreciate you even looking into this for me.. At least I dont feel like I am being chewed out about my design.

This definitely goes where I am thinking. My initial question was asking however if there was a way txtbox1,2,3...7 could be put into an array and allow for less lines of coding and better control later down the road. I have not played a great deal with arrays and didnt know how to check individual elements for null.

So say for instance

dim txtbox as textbox

if txtbox (n-1) is null then
some code
else
'check next txtbox in array to see which is available then some code

 

[CBrighton]

What I would do instead is create a function.

The only difference between doing this on one control compared to another is the name of the control. In each instance you will want to repeat the same control name with a different number on the end.

If you code it once you should be able to change it into a function which would allow you to pass the control name as an arguement.

PostToFirstAvailable("txtID")

If you set the function to use a control name from the arguement and concat on whichever number you are on in the 1-7 loop then it should just be 1 module with the code then a single line to call it each time.

 

[tt1611]

Wow Chris
This is the point at which I say no speako Englis...
Ok

I get

PostToFirstAvailable("txtID")

once the function is created..

The question is writing such a function that uses a textbox name as parameter. I am almost clueless as to how this would be written.

 

[CBrighton]

Rather than having something like:

Public Sub Form_Open
'blah blah blah
End Sub

You will do something like this:

Public Function PostToFirstAvailable(ControlName as string)
'blah blah blah
End function

The important part is that because we have ControlName as a parameter, each time ControlName is in the VBA the function will substitute that for whatever paramater we provide.

So any time you want to do something to the control, you just use the string ControlName.




However, this is pseudocode to show a concept so I haven't bothered with naming conventions, etc.