Need Assistance Again

wind20mph

wind20mph

MS Access User Since 1996
Local time
Today, 17:09
Joined
Mar 5, 2013
Messages
50
MS Access 2013

Adding Data Using VBA On_click function. Any help is highly appreciated.

Runtime Error '3265'
"Item not found in this collection"

Produced by the code below (OnClick Function)
Code: 
Option Compare Database

Private Sub cmdPost_Click()
Dim dbASP As DAO.Database
Dim rsASP As DAO.Recordset
Dim intASP As Integer
Dim strASP As String
Dim strHBK As String

Set dbASP = CurrentDb
strASP = "SELECT * FROM tblList;"
Set rsASP = dbASP.OpenRecordset(strASP)
If rsASP.RecordCount <= 0 Then
    If IsNull(Me![LastName]) Then
        Me![LastName].Value = "Jing" & rsASP.RecordCount
    ElseIf IsNull(Me![FirstName]) Then
        Me![LastName].Value = "Magdato" & rsASP.RecordCount
    ElseIf IsNull(Me![MiddleName]) Then
        Me![MiddleName].Value = "P."
    ElseIf IsNull(Me![EmailAddress]) Then
        Me![EmailAddress].Value = "hbk@scdcnet.biz"
    ElseIf IsNull(Me![DateOfTrans]) Then
        Me![DateOfTrans].Value = #7/9/1976#
    Else
    rsASP.AddNew
    rsASP.Fields("LastName") = Me![LastName]
    rsASP.Fields("FirstName") = Me![FirstName]
    rsASP.Fields("MiddleName") = Me![MiddleName]
    rsASP.Fields("EmailAddress") = Me![EmailAddress]
    strHBK = Me![DateOfTrans].Value
    'strHBK = Left(strHBK, 2) & "/" & Left(Right(strhkb, 2), 4) & "/" & Right(strHBK, 2)
    rsASP.Fields(DateOfTrans) = Format(strHBK, "ShortDate")
    rsASP.Update
    rsASP.Requery
    MsgBox "thre are " & rsASP.RecordCount & " record added!"
    rsASP.Close
    dbASP.Close
    End If
Else
    If rsASP.RecordCount > 0 Then
    If IsNull(Me![LastName]) Then
        Me![LastName].Value = "Jing" & rsASP.RecordCount
    ElseIf IsNull(Me![FirstName]) Then
        Me![LastName].Value = "Magdato" & rsASP.RecordCount
    ElseIf IsNull(Me![MiddleName]) Then
        Me![MiddleName].Value = "P."
    ElseIf IsNull(Me![EmailAddress]) Then
        Me![EmailAddress].Value = "hbk@scdcnet.biz"
    ElseIf IsNull(Me![DateOfTrans]) Then
        Me![DateOfTrans].Value = #7/9/1976#
    Else
    rsASP.AddNew
    rsASP.Fields("LastName") = Me![LastName]
    rsASP.Fields("FirstName") = Me![FirstName]
    rsASP.Fields("MiddleName") = Me![MiddleName]
    rsASP.Fields("EmailAddress") = Me![EmailAddress]
    strHBK = Format(Me![DateOfTrans], "mm/dd/yy")
    'strHBK = Left(strHBK, 2) & "/" & Left(Right(strhkb, 2), 4) & "/" & Right(strHBK, 2)
    rsASP.Fields(DateOfTrans) = strHBK
    rsASP.Update
    rsASP.Requery
    MsgBox "thre are " & rsASP.RecordCount & " record added!"
    rsASP.Close
    dbASP.Close
    End If
End If
End If
End Sub

Hope to understand this more. Thanks in Advance.
 
bob fitz said:
Which line throws the error.
Click to expand...
Code: 
     strHBK = Format(Me![DateOfTrans], "mm/dd/yy")     'strHBK = Left(strHBK, 2) & "/" & Left(Right(strhkb, 2), 4) & "/" & Right(strHBK, 2)     rsASP.Fields(DateOfTrans) = strHBK
This line throws the error.

but if i replace the rsASP.Fields(DateOfTrans) to rsASP![DateOfTrans] it saves the data, however it only saves the date "5/12/10" instead of what is placed in the textbox.

thanks for the prompt reply.
 
Error means that DateoOfTrans is not found on your form, assuming the error is on this line:
strHBK = Format(Me![DateOfTrans], "mm/dd/yy")

or if your error is actually on the rsASP part (you seem to have quoted 3 lines instead of one)

rsASP.Fields(DateOfTrans) = Format(strHBK, "ShortDate")
you need " " like on other fields:
rsASP.Fields("DateOfTrans") = Format(strHBK, "ShortDate")
 
I have replaced with this code:

Code: 
rsASP.AddNew
    rsASP.Fields("LastName") = Me![LastName]
    rsASP.Fields("FirstName") = Me![FirstName]
    rsASP.Fields("MiddleName") = Me![MiddleName]
    rsASP.Fields("EmailAddress") = Me![EmailAddress]
    strHBK = Format(Me![DateOfTrans], "mm/dd/yy")
   Right(strHBK, 2)
    rsASP![DateOfTrans] = strHBK
    rsASP.Update
    rsASP.Requery
    MsgBox "thre are " & rsASP.RecordCount & " record added!"
    rsASP.Close
    dbASP.Close
no error pop-up but wrong date was posted into the table. I made at least 5 records and the same date appeared "5/16/10"

These are the records:
tblList TMPID LastName FirstName MiddleName EmailAddress DateOfTrans 23 Magdato Jing P. hbk@scdcnet.biz 05/16/10 24 Lorino Junzon M. jlas@yahoo.com 05/16/10 25 Momma Al Jabbar M. alsm@gmail.com 10/28/09 26 Dolphin Samonito S. amos1111@yahoo.com 02/05/10 27 Labanda Ariel T. lta@myramork.com 04/02/14
 
Last edited:
I strongly suggest you save yourself the headache of using two different notations...

Either use: rsASP.Fields("LastName")
Or use: rsASP![DateOfTrans]
Not mix the two....

My best guess for the mixed up dates is that you are not entering the date properly.
is the DateOfTrans on your form an actual date control? Or a text control?
How are you entering the data?
 
I got it.

I have added the following codes:
Code: 
Private Sub cmdPost_Click()
Dim dbASP As DAO.Database
Dim rsASP As DAO.Recordset
Dim intASP As Integer
Dim strASP As String
Dim strHBK As String

Set dbASP = CurrentDb
strASP = "SELECT * FROM tblList;"
Set rsASP = dbASP.OpenRecordset(strASP)
If rsASP.RecordCount <= 0 Then
    If IsNull(Me![LastName]) Then
        Me![LastName].Value = "Jing" & rsASP.RecordCount
    ElseIf IsNull(Me![FirstName]) Then
        Me![LastName].Value = "Magdato" & rsASP.RecordCount
    ElseIf IsNull(Me![MiddleName]) Then
        Me![MiddleName].Value = "P."
    ElseIf IsNull(Me![EmailAddress]) Then
        Me![EmailAddress].Value = "hbk@scdcnet.biz"
    ElseIf IsNull(Me![DateOfTrans]) Then
        Me![DateOfTrans].Value = #7/9/1976#
    Else
    rsASP.AddNew
    rsASP.Fields("LastName") = Me![LastName]
    rsASP.Fields("FirstName") = Me![FirstName]
    rsASP.Fields("MiddleName") = Me![MiddleName]
    rsASP.Fields("EmailAddress") = Me![EmailAddress]
    strHBK = Me![DateOfTrans].Value
    [COLOR=Red]Me.DateOfTrans.Format = "ShortDate"[/COLOR]
    rsASP.Fields("DateOfTrans") = Format(strHBK, "mm/dd/yy")
    rsASP.Update
    rsASP.Requery
    MsgBox rsASP.RecordCount & " record added!"
    rsASP.Close
    dbASP.Close
    End If
Else
    If rsASP.RecordCount > 0 Then
    If IsNull(Me![LastName]) Then
        Me![LastName].Value = "Magdato" & rsASP.RecordCount
    ElseIf IsNull(Me![FirstName]) Then
        Me![FirstName].Value = "Jing" & rsASP.RecordCount
    ElseIf IsNull(Me![MiddleName]) Then
        Me![MiddleName].Value = "P."
    ElseIf IsNull(Me![EmailAddress]) Then
        Me![EmailAddress].Value = "hbk@scdcnet.biz"
    ElseIf IsNull(Me![DateOfTrans]) Then
        Me![DateOfTrans].Value = #7/9/1976#
    Else
    rsASP.AddNew
    rsASP.Fields("LastName") = Me![LastName]
    rsASP.Fields("FirstName") = Me![FirstName]
    rsASP.Fields("MiddleName") = Me![MiddleName]
    rsASP.Fields("EmailAddress") = Me![EmailAddress]
    strHBK = Format(Me![DateOfTrans], "mm/dd/yy")
    [COLOR=Red]Me.DateOfTrans.Format = "ShortDate"[/COLOR]
    rsASP.Fields("DateOfTrans") = Format(strHBK, "mm/dd/yy")
    rsASP.Update
    rsASP.Requery
    MsgBox "thre are " & rsASP.RecordCount & " record added!"
    rsASP.Close
    dbASP.Close
    End If
End If
End If
End Sub
Problem Solved!
 
You must log in or register to reply here.

Similar threads

B
Inconsistent error
Replies
6
Views
930
bretdunlap
B
Z
Strange crash of Access...
Replies
5
Views
1,809
zozew
Z

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom