NotInList error for new entries

[Guest]

I have two tables. One with Categories and one with the categories ID linked in as a foreign key (basetable). I then use a lookup and hides the ID field to get a string shown in my form.

When the Category typed is not in the category list, I would like it automaticly added to the Category table. and used as dataentry and now beeing part of the list for the next entry (could be used in another table/form). Also I would like the MS error message Not In List not showing up!

I guess I am going to do something in the NotInList event ... I posted a question yesterday and thanks to Jonathan I got the code in the bottom of this letter. I have worked with it and I can sinmply not get a grasp on it (probably since I am very novice when it comes to VB)

Any simple ideas out there?

Best wishes Reno Lindberg

I erased some linebreaks and added As String in the private sub cboActivity_NotInList(NewData As String,Response As String). AND I changed the names of cboActivity, Activity, New Activity etc ....But it will simply not run


FromJonathan;

private sub cboActivity_NotInList(NewData,Response)
dim rst as recordset

if msgbox(newdata & "... not in list, add
it?",vbokcancel,"New Activity")=vbok then
set rst=....
with rst
.addnew
.fields("Activity")=newdata
.update
.close
end with
set rst=nothing
response=acdataerradded
else
response=acdataerrcontinue
end if
end sub

 

[Wayne Morgan]

What Jonathan gave you looks good. Response is NOT a string. acDataErrAdded
and acDataErrContinue are built in constants that evaluate to 2 and 0. Leave
the Private Sub line the way Access creates it for you automatically when
you click on the ... button after choosing <Event Procedure> on the Event
tab of the combo box's Properties sheet. Also, you need to set the Set rst=
statement to the table you want to add the data to. Are there just 2 field
in your Categories table, the category and an autonumber?

I recommend changing the Dim rst as Recordset to

Dim rst As DAO.Recordset

Also, you'll need to make sure that you have a Reference set to DAO if you
have Access 2000 or newer. In the code window go to Tools|References and
make sure that there is a check next to "Microsoft DAO 3.6 Object Library".

--
Wayne Morgan
MS Access MVP

I have two tables. One with Categories and one with the categories ID

linked in as a foreign key (basetable). I then use a lookup and hides the ID
field to get a string shown in my form.

When the Category typed is not in the category list, I would like it

automaticly added to the Category table. and used as dataentry and now
beeing part of the list for the next entry (could be used in another
table/form). Also I would like the MS error message Not In List not showing
up!

I guess I am going to do something in the NotInList event ... I posted a

question yesterday and thanks to Jonathan I got the code in the bottom of
this letter. I have worked with it and I can sinmply not get a grasp on it
(probably since I am very novice when it comes to VB)

Any simple ideas out there?

Best wishes Reno Lindberg

I erased some linebreaks and added As String in the private sub

cboActivity_NotInList(NewData As String,Response As String). AND I changed
the names of cboActivity, Activity, New Activity etc ....But it will simply
not run

 

[Guest]

NotInList Code does not work for me

When I run the code at the bottom of this message I get several massages;
Stops at .Fields; error says "Compile error: Invalid use of property"
If I erase .Fields the program stops at Else without If command... How can that be, Since there is a very clear If statement at the top?

I have two tables
1. table with two columns LKP_MoneySourceSub.MoneySourceColID and LKP_MoneySourceSub.MoneySourceSubType
2. Table with three collumns TBL_P3_17_ProductConstructCosts.ProductConstructID, TBL_P3_17_ProductConstructCosts.FK_MoneySource, TBL_P3_17_ProductConstructCosts.FK_MoneySourceSub,
The two columns in table 2 starting with FK-.... is foreign keys to lookuptables with categories.
Table 1 is one of the two category tables

If a user enters a category in a form that is not on the list, the following code is excecuted... but it doesnt work for me.. can you please comment on what I am doing wrong
I have unsuccesfully tried to find information on the With statenment .Fields but it is imposible to search the help file for a common word as With. Or .Fields which give Fields As Fileds which give no meaning.

Here is the code I try to run

Private Sub FK_MoneySourceSub_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset

If MsgBox(NewData & "... not in list, add it?", vbOKCancel, "New MoneySource") = vbOK Then Set rst = [LKP_MoneySourceSub]
With rst
.AddNew
.Fields ("Activity")
.Update
.Close
End With
Set rst = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Sorry for sounding a little tired, but what I thought was a simple task have now taken me all weekend with no succes
And with an Internet connectionthat at best works awful .....
But anyway thank you very much for the time you have used uptil now

Best wishes
Reno

 

[Guest]

My last answer posting did not show up, so here it comes in somewhat shorter versio

Yes my Category tables only have two columns one autonumber and one text (which is the Primary Key

Yes I am running Acces 2000. I now have turned Microsoft DAO 3.6 Object Library on

Is it necessary to turn this feature on, on other maschines that are going to run this database?. I am working on a research project in Vietnam, where we are interviewing som Vietnamese farmers about their farming system and succes. Therefore some fieldworkers are going to put in data and the will problabluy use their own computers to do taht (I will just give them a copy.. one at a time
That all
Thanks for your help... and please read my other posting about aaaalll my problem

Best wishe
Ren

 

[Guest]

I found some of the errors! Some was made by me and some was because of the copy-paste thing from the list. When I added the right number of spces so If was instraight line up from Else, it seemed to solve my problem with No If command for the Else command error.

I gues my next problem is in the adressing buisness. The statement "Set rst = ..." You asked me to put in the table name. Should that be put in with aquare brachets as you see hereunder? Should it be the table name, the tablename.columnname or how to refer to that field in another table?
Is it right that I have to put the columnname in under ".Fields ([MoneySourceSubType]) and how about braskets etc?

When I run the code I get an error 2465 after I click OK on the Msgbox. It says that "MS Acces cant find the field "│" refered to in your expresion". I am, not unexpectably, able to geneate a lot of other errors, but it seems this is the furtest I can get for now.

Please see the code with fewer errors hereunder

Best Wishes
Reno Lindberg


Private Sub FK_MoneySourceSub_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset

If MsgBox(NewData & "... not in list, add it?", vbOKCancel, "New MoneySource") = vbOK Then
Set rst = [MoneySourceSub.MoneySourceSubType]
With rst
.AddNew
.Fields([MoneySourceSubType]) = NewData
.Update
.Close
End With
Set rst = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

 

[Jonathan Parminter]

Please see the code with fewer errors hereunder

Best Wishes
Reno Lindberg


Private Sub FK_MoneySourceSub_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset

If MsgBox(NewData & "... not in list, add it?",

vbOKCancel, "New MoneySource") = vbOK Then

Set rst = [MoneySourceSub.MoneySourceSubType]
With rst
.AddNew
.Fields([MoneySourceSubType]) = NewData
.Update
.Close
End With
Set rst = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
.

Hi Reno, you might gain a bit from using online help to
lookup recordsets.

the set line should look something like....
Set rst = currentdb.openrecordset("table or query name")

the assignment line should look something like...
..Fields("MoneySourceSubType") = NewData

Luck
Jonathan

 

[Guest]

Thanks Johanathan
I have been around in the help, but not for recordset ... sometimes it is a problem to know what to look for....
I tried to look for the With command ... BUT With is in all pages .. and so on...
Now I will look for recordset .... I hope everything will work out now

Best wishes and thanks

Reno Lindberg

 

[Guest]

Thanks to Jonathan and Wayne, my problem was solved. I here post the last code that works for m

Best Wishe
Reno Lindber

Private Sub FK_MoneySourceSub_NotInList(NewData As String, Response As Integer
Dim rst As DAO.Recordse

If MsgBox(NewData & "... not in list, add it?", vbOKCancel, "New MoneySource") = vbOK The
Set rst = CurrentDb.OpenRecordset("MoneySourceSub"
With rs
.AddNe
.Fields("MoneySourceSubType") = NewDat
.Updat
.Clos
End Wit
Set rst = Nothin
Response = acDataErrAdde
Els
Response = acDataErrContinu
End I
End Sub