I did some "fiddling" and ended up using VLookup. I assigned my database list "rows" with an ID. So the employee opens the router form by clicking button...form opens employee enters the ID #...clicks in an empty field in the form and the fields on the router sheet automatically populates then they click print and done...I also made a "Store Information" button so the router sheet can be used to enter data into the database list...BUT I've ran into another issue.
I created a cmdSend button so when clicked the code below looks for the next available empty row in my database list and stores what is currently populated in the router form in that empty row.
What I need is when the form is used to "store" data an ID in not entered and that field on the router form is left blank. I need the code below to store the data as usual (in the next empty row) but if there is no data in the ID control/field of the router form, the code looks at the ID column/row in the database list that has an ID number and increments it by 1 and stores the new ID in the row/cell under the ID column of the current data being stored.
This could be done by itself when the form is closed by looking for the next empty "cell" in the ID column and enter a number that's 1 more that the cell above...point being is the ID is only important when "pulling" data not storing it.
So Keeping to that train of thought maybe generating a number in the ID control field itself by incrementing the last ID number found in the ID column/row database...that would be even better because it wouldn't be stored unless the user hits the cmdSend button. and if they're looking up material only, they would just erase the generated ID number and enter the ID they need to being up the information they're looking for...I going to work on that one
Code:
'Dim the variables
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range
'change the number for the number of controls/text boxes on the userform
cNum = 17
Set nextrow = Sheet3.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
MsgBox "The data has been sent"
'Clear the controls
cNum = 17
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Set nextrow = nextrow.Offset(0, 1)
Next
End Sub