NotinList

[Gina]

I am having big problems with this because I basically don't understand how
to code at all.

What I have is a little db that has a a notinlist situation.

In tbl_equipment there is a key consisting of the following two fields

Building
Equipment

In tbl_tasklist in I have a lookup to tbl_equipment

SELECT Tbl_Equipment.Building, Tbl_Equipment.Equipment FROM Tbl_Equipment
ORDER BY [Building], [Equipment];

This field is limited to list.


Then I made two forms:

Frm_Tasklist and Frm_Equipment



I'm trying (and failing) at creating code for when Frm_Tasklist is open, and
a new piece of equipment is typed in to the Building-Equip field, how to get
it to add/update.


I've tried reading the access-help information, and I keep messing up.
Any help is greatly appreciated.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NotInList.mdb" which illustrates several scenerios involving
Not In List. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=311

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Gina]

I appreciate the links. I'm still having trouble though and am starting to
think to hell with the lists.

Private Sub Building_Equip_NotInList(NewData As String, Response As Integer)

Dim db As Database, rs As Recordset, strMsg As String

strMsg = NewData & " is not listed ! Do you want to add it to the list?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Building-Equipment ?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_equipment", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!building = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub


I don't know what I'm doing wrong.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NotInList.mdb" which illustrates several scenerios involving
Not In List. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=311

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I am having big problems with this because I basically don't understand how
to code at all.

What I have is a little db that has a a notinlist situation.

In tbl_equipment there is a key consisting of the following two fields

Building
Equipment

In tbl_tasklist in I have a lookup to tbl_equipment

SELECT Tbl_Equipment.Building, Tbl_Equipment.Equipment FROM Tbl_Equipment
ORDER BY [Building], [Equipment];

This field is limited to list.


Then I made two forms:

Frm_Tasklist and Frm_Equipment



I'm trying (and failing) at creating code for when Frm_Tasklist is open,
and
a new piece of equipment is typed in to the Building-Equip field, how to
get
it to add/update.


I've tried reading the access-help information, and I keep messing up.
Any help is greatly appreciated.

 

[Roger Carlson]

I think I know what your problem is. In your first post you said:
"In tbl_tasklist in I have a lookup to tbl_equipment"

If that means you have a LookUp field to another table, this is the cause of
your problem. GET RID OF THE LOOKUP! You think you are adding the correct
value with your NotInList code, but you're not. Please read this link:
http://www.mvps.org/access/lookupfields.htm


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I appreciate the links. I'm still having trouble though and am starting to
think to hell with the lists.

Private Sub Building_Equip_NotInList(NewData As String, Response As
Integer)

Dim db As Database, rs As Recordset, strMsg As String

strMsg = NewData & " is not listed ! Do you want to add it to the list?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Building-Equipment ?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_equipment", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!building = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub


I don't know what I'm doing wrong.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NotInList.mdb" which illustrates several scenerios
involving
Not In List. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=311

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I am having big problems with this because I basically don't understand
how
to code at all.

What I have is a little db that has a a notinlist situation.

In tbl_equipment there is a key consisting of the following two fields

Building
Equipment

In tbl_tasklist in I have a lookup to tbl_equipment

SELECT Tbl_Equipment.Building, Tbl_Equipment.Equipment FROM
Tbl_Equipment
ORDER BY [Building], [Equipment];

This field is limited to list.


Then I made two forms:

Frm_Tasklist and Frm_Equipment



I'm trying (and failing) at creating code for when Frm_Tasklist is
open,
and
a new piece of equipment is typed in to the Building-Equip field, how
to
get
it to add/update.


I've tried reading the access-help information, and I keep messing up.
Any help is greatly appreciated.

 

[Gina]

If I get rid of the lookup to the equipment table from the tasklist table,
then I only have a text field and no limit to list- therefore have no problem
in the form. However I then lose the ability to keep an equipment list with
unique values.

I think I know what your problem is. In your first post you said:
"In tbl_tasklist in I have a lookup to tbl_equipment"

If that means you have a LookUp field to another table, this is the cause of
your problem. GET RID OF THE LOOKUP! You think you are adding the correct
value with your NotInList code, but you're not. Please read this link:
http://www.mvps.org/access/lookupfields.htm


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I appreciate the links. I'm still having trouble though and am starting to
think to hell with the lists.

Private Sub Building_Equip_NotInList(NewData As String, Response As
Integer)

Dim db As Database, rs As Recordset, strMsg As String

strMsg = NewData & " is not listed ! Do you want to add it to the list?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Building-Equipment ?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_equipment", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!building = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub


I don't know what I'm doing wrong.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NotInList.mdb" which illustrates several scenerios
involving
Not In List. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=311

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I am having big problems with this because I basically don't understand
how
to code at all.

What I have is a little db that has a a notinlist situation.

In tbl_equipment there is a key consisting of the following two fields

Building
Equipment

In tbl_tasklist in I have a lookup to tbl_equipment

SELECT Tbl_Equipment.Building, Tbl_Equipment.Equipment FROM
Tbl_Equipment
ORDER BY [Building], [Equipment];

This field is limited to list.


Then I made two forms:

Frm_Tasklist and Frm_Equipment



I'm trying (and failing) at creating code for when Frm_Tasklist is
open,
and
a new piece of equipment is typed in to the Building-Equip field, how
to
get
it to add/update.


I've tried reading the access-help information, and I keep messing up.
Any help is greatly appreciated.

 

[Gina]

i've wasted 6 hours on this. I give up.

If I get rid of the lookup to the equipment table from the tasklist table,
then I only have a text field and no limit to list- therefore have no problem
in the form. However I then lose the ability to keep an equipment list with
unique values.

I think I know what your problem is. In your first post you said:
"In tbl_tasklist in I have a lookup to tbl_equipment"

If that means you have a LookUp field to another table, this is the cause of
your problem. GET RID OF THE LOOKUP! You think you are adding the correct
value with your NotInList code, but you're not. Please read this link:
http://www.mvps.org/access/lookupfields.htm


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I appreciate the links. I'm still having trouble though and am starting to
think to hell with the lists.

Private Sub Building_Equip_NotInList(NewData As String, Response As
Integer)

Dim db As Database, rs As Recordset, strMsg As String

strMsg = NewData & " is not listed ! Do you want to add it to the list?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Building-Equipment ?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_equipment", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!building = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub


I don't know what I'm doing wrong.

:

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NotInList.mdb" which illustrates several scenerios
involving
Not In List. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=311

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I am having big problems with this because I basically don't understand
how
to code at all.

What I have is a little db that has a a notinlist situation.

In tbl_equipment there is a key consisting of the following two fields

Building
Equipment

In tbl_tasklist in I have a lookup to tbl_equipment

SELECT Tbl_Equipment.Building, Tbl_Equipment.Equipment FROM
Tbl_Equipment
ORDER BY [Building], [Equipment];

This field is limited to list.


Then I made two forms:

Frm_Tasklist and Frm_Equipment



I'm trying (and failing) at creating code for when Frm_Tasklist is
open,
and
a new piece of equipment is typed in to the Building-Equip field, how
to
get
it to add/update.


I've tried reading the access-help information, and I keep messing up.
Any help is greatly appreciated.

 

[Gina]

thanks for trying to help though

I think I know what your problem is. In your first post you said:
"In tbl_tasklist in I have a lookup to tbl_equipment"

If that means you have a LookUp field to another table, this is the cause of
your problem. GET RID OF THE LOOKUP! You think you are adding the correct
value with your NotInList code, but you're not. Please read this link:
http://www.mvps.org/access/lookupfields.htm


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I appreciate the links. I'm still having trouble though and am starting to
think to hell with the lists.

Private Sub Building_Equip_NotInList(NewData As String, Response As
Integer)

Dim db As Database, rs As Recordset, strMsg As String

strMsg = NewData & " is not listed ! Do you want to add it to the list?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Building-Equipment ?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_equipment", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!building = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub


I don't know what I'm doing wrong.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NotInList.mdb" which illustrates several scenerios
involving
Not In List. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=311

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I am having big problems with this because I basically don't understand
how
to code at all.

What I have is a little db that has a a notinlist situation.

In tbl_equipment there is a key consisting of the following two fields

Building
Equipment

In tbl_tasklist in I have a lookup to tbl_equipment

SELECT Tbl_Equipment.Building, Tbl_Equipment.Equipment FROM
Tbl_Equipment
ORDER BY [Building], [Equipment];

This field is limited to list.


Then I made two forms:

Frm_Tasklist and Frm_Equipment



I'm trying (and failing) at creating code for when Frm_Tasklist is
open,
and
a new piece of equipment is typed in to the Building-Equip field, how
to
get
it to add/update.


I've tried reading the access-help information, and I keep messing up.
Any help is greatly appreciated.