Can I use "not in the list" to use date format.

[Linda]

I am a novice who is self taught from surfing and trial and error learning
Excel and Access. I am the only one who is entering information. I have a
form that consists of purchaseplace, category, name, cost and date. I have
figured out how to get the "not in list" would you like to add? to work for
purchaseplace, and category but not for date. I would like to use long
format and I am thinking that this event won't work for this format. Am I
correct or is there a way?

Thanks Linda

 

[Douglas J. Steele]

What's your code look like?

 

[Linda]

Private Sub DateID_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Date...")
If i = vbYes Then
strSQL = "Insert Into DateT ([Date]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

this part works okay, but when I try to add a date it won't accept. For
example if try to add as is this 06/18/08 it says 06/18/08 is not currently
in the likst.
Do you want to add it?

When I hit yes
It says it is currently not in the list
I click ok.
It enters the date as 06/18/08. If I try to enter the same date again it
does the same thing and I end up with the same date twice. What I would like
to know is it possible to enter 06/18/08 and have it show in long format in
the combo box.

I do have it set to no duplicates as well.

Thanks Linda

 

[Douglas J. Steele]

Dates in Access must be delimited with #, not quotes (and must be in a
format that Access will recognize).

Change

strSQL = "Insert Into DateT ([Date]) " & _
"values ('" & NewData & "');"

to

strSQL = "Insert Into DateT ([Date]) " & _
"values (" & Format(NewData, "\#yyyy\-mm\-dd\#") & ");"

Incidentally, you should rename your field. Date is a reserved word, and
should never be used for your own purposes. For a comprehensive list of
names to avoid (as well as a link to a utility that will check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Private Sub DateID_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Date...")
If i = vbYes Then
strSQL = "Insert Into DateT ([Date]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

this part works okay, but when I try to add a date it won't accept. For
example if try to add as is this 06/18/08 it says 06/18/08 is not
currently
in the likst.
Do you want to add it?

When I hit yes
It says it is currently not in the list
I click ok.
It enters the date as 06/18/08. If I try to enter the same date again it
does the same thing and I end up with the same date twice. What I would
like
to know is it possible to enter 06/18/08 and have it show in long format
in
the combo box.

I do have it set to no duplicates as well.

Thanks Linda

What's your code look like?

 

[Linda]

Thank you so much, I just copied the code from somewhere else and didn't know
about the changes.
Also thanks for the link on what not to name your fields.

Linda

Dates in Access must be delimited with #, not quotes (and must be in a
format that Access will recognize).

Change

strSQL = "Insert Into DateT ([Date]) " & _
"values ('" & NewData & "');"

to

strSQL = "Insert Into DateT ([Date]) " & _
"values (" & Format(NewData, "\#yyyy\-mm\-dd\#") & ");"

Incidentally, you should rename your field. Date is a reserved word, and
should never be used for your own purposes. For a comprehensive list of
names to avoid (as well as a link to a utility that will check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Private Sub DateID_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Date...")
If i = vbYes Then
strSQL = "Insert Into DateT ([Date]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

this part works okay, but when I try to add a date it won't accept. For
example if try to add as is this 06/18/08 it says 06/18/08 is not
currently
in the likst.
Do you want to add it?

When I hit yes
It says it is currently not in the list
I click ok.
It enters the date as 06/18/08. If I try to enter the same date again it
does the same thing and I end up with the same date twice. What I would
like
to know is it possible to enter 06/18/08 and have it show in long format
in
the combo box.

I do have it set to no duplicates as well.

Thanks Linda

What's your code look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am a novice who is self taught from surfing and trial and error
learning
Excel and Access. I am the only one who is entering information. I
have a
form that consists of purchaseplace, category, name, cost and date. I
have
figured out how to get the "not in list" would you like to add? to work
for
purchaseplace, and category but not for date. I would like to use
long
format and I am thinking that this event won't work for this format. Am
I
correct or is there a way?

Thanks Linda