VBA Code Help Please

[jo15765]

I get this error on my form:

The following runtime error occured:
Number: 3265
Description: Item not found in this collection
Object Type: Form
Procedure: No_LostFocus
Object: frmMain

I changed this code: (which works perfectly)

Private Sub No_LostFocus()
Dim t_cbo_name
Dim strsql AS String
Dim db AS database
Dim rec AS Recordset
Set db = currentDB
Set rec = db.OpenRecordset("tbl_initial_seeder")

Do until rec.EOF
with rec
   If Trim(!login_id) = Trim(Me.txt_winusername) Then
   t_name = ![id]
   t_cbo_name = !p
      If IsNull(Me.Data_Input_by_name_box) Then
      Me.Data_Input_by_name_box = t_cbo_name
      Else
      End If

Else

End If
.MoveNext

End With
Loop

Set rec = Nothing
Me.WinUserName_box = txt_winusername
Me.date_updated = Now()
Debug.Print t_name, Me.txt_winusername, t_cbo_name

Do.Cmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_no_LostFocus()

Exit Sub

To this code which is what produces the error I posted above

Private Sub No_LostFocus()
Dim t_cbo_name
Dim strsql AS String
Dim db AS database
Dim rec AS Recordset
Set db = currentDB
Set rec = db.OpenRecordset("tbl_j")

Do until rec.EOF
with rec
   If Trim(!login_id) = Trim(Me.txt_winusername) Then
   t_name = ![id]
   t_cbo_name = !Name
      If IsNull(Me.Record_Input_By) Then
      Me.Data_Input_by_name_box = t_cbo_name
      Else
      End If

Else

End If
.MoveNext

End With
Loop

Set rec = Nothing
Me.WinUserName_box = txt_winusername
Me.date_updated = Now()
Debug.Print t_name, Me.txt_winusername, t_cbo_name

Do.Cmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_no_LostFocus()

Exit Sub

I am wanting to change the db.OpenRecordset to another table (table_j)
The t_name = ![id] ---- I am assuming this is fine, since both tables have "id"
the t_cbo_name = !p --- I am trying to change that to name, because I want it to display the name in the table I am trying to change over to

[RuralGuy]

Looking things over rather quickly, be sure you have not used a Reserved word as the name of *any* object: table, field, control, form, etc. Here's a good reference with which to check: http://www.allenbrowne.com/AppIssueBadWord.html
There are ways around it but it is far better to simply change the name used. Access gets confused and cannot determine to what you are referring.

[jo15765]

Ah, that is a long list, I didn't know about it, thanks for the valuable link! I see that the word "Name" is on the list, and I am trying to use that word. Will I need to change it on my table as well, since that is what I am referencing in the VB Code?

[jo15765]

I changed the word "Name" as it was on the list, but that is the only word that jumped out at me from the list, but I still receive the same error?

[RuralGuy]

Some basic rules of thumb to follow:
1) Always put Option Explicit at the top of your class module to force defining variables before using them.
2) The DoMenuItem syntax is basically obsolete these days. Change to the DoCommand systax. Here's a link for translation: http://www.accessruncommand.com/domenuitem.htm
3) Disambiguate your variables. Instead of
Dim db As Database
...it should be...
Dim db As DAO.Database
Dim rec As DAO.Recordset
4) If you Set something in your procedure then Set it to Nothing before leaving the procedure. That means *every* object you Set.
5) You should also .Close a Recordset before Setting it to Nothing.
Does the debugger highlight the line is does not like?

[jo15765]

The Debugger does not display any issues, the error pops up when I reach that point on the form. I will try the other suggestions that you offered, and post back when I have exhausted those options.

[jo15765]

I followed:
4) If you Set something in your procedure then Set it to Nothing before leaving the procedure. That means *every* object you Set.

And that corrected the Error on the form, I was getting! Thanks for your help.

[ajetrumpet]

I followed:
4) If you Set something in your procedure then Set it to Nothing before leaving the procedure. That means *every* object you Set.

And that corrected the Error on the form, I was getting! Thanks for your help.

Very nice. I was just looking at your code in split screen and comparing it. Outcomes like this are so interesting, yet overlooked all the time. I'm at this stage right now in javascript.

[RuralGuy]

Excellent! Thanks for posting back with your success.