Question with Form (If statement)

[SD23]

I have a combo box on one form filled with inventor names. There are 10 of these combo boxes. If any number of these are filled, I want it to take the names and automatically put it in another form under inventor names. RIght now I have used a If then statement, but it only transfers the names to the other form when I input all 10 names. I was wondering if someone could help me with the code so that even if 1, 2, 3, or 4 names are filled, they get updated to the next form. Here is part of the code

If (Not IsNull(Me.invent1)) Then query = query & " And [Inventor 1] = '" & Me.invent1 & "'"
If (Not IsNull(Me.invent2)) Then query = query & " And [Inventor 2] = '" & Me.invent2 & "'"
If (Not IsNull(Me.invent3)) Then query = query & " And [Inventor 3] = '" & Me.invent3 & "'"
If (Not IsNull(Me.invent4)) Then query = query & " And [Inventor 4] = '" & Me.invent4 & "'"
If (Not IsNull(Me.invent5)) Then query = query & " And [Inventor 5] = '" & Me.invent5 & "'"
If (Not IsNull(Me.invent6)) Then query = query & " And [Inventor 6] = '" & Me.invent6 & "'"
If (Not IsNull(Me.invent7)) Then query = query & " And [Inventor 7] = '" & Me.invent7 & "'"
If (Not IsNull(Me.invent8)) Then query = query & " And [Inventor 8] = '" & Me.invent8 & "'"
If (Not IsNull(Me.invent9)) Then query = query & " And [Inventor 9] = '" & Me.invent9 & "'"
If (Not IsNull(Me.invent10)) Then query = query & " And [Inventor 10] = '" & Me.invent10 & "'"


Set rst = db.OpenRecordset(query)

If rst.EOF Or rst.BOF Then
MsgBox "This is a new docket number currently not present in All Data Table. Click OK to create it and on the next screen verify the information and add or remove the invention disclosure."

DoCmd.SetWarnings False

query2 = "Insert into [All Data Table]([Date Created Docket No], [Docket No Prefix], [Docket No],[Docket Country], [Docket No Suffix], [Title], [Inventor 1], [Inventor 2], [Inventor 3], [Inventor 4], [Inventor 5], [Inventor 6], [Inventor 7], [Inventor 8], [Inventor 9], [Inventor 10]) Values(#" & Date & "#,'" & Me.dpre.Value & "','" & Me.dnum & "','" & Me.dcount & "','" & Me.dsuf.Value & "','" & Me.Title1 & "','" & Me.invent1 & "','" & Me.invent2 & "','" & Me.invent3 & "','" & Me.invent4 & "','" & Me.invent5 & "','" & Me.invent6 & "','" & Me.invent7 & "','" & Me.invent8 & "','" & Me.invent9 & "','" & Me.invent10 & "')"

DoCmd.RunSQL query2

 

[KenHigg]

a. Shouldn't this be using 'or' logic
b. I would try getting this type stuff work with say just 3 options, then you can simple add options with copies of slightly modified portions of your code...