Updating a Table from a Form Entry

[Steve Smith via AccessMonster.com]

Hi, I appologise if this has been asked. I am setting up a simple database that contains a lookup. This loopkup is based on a simple query. Ok let me tell you how it is setup. Table 1 contains 2 fields...autonumber as key and Suburb. A query has been created and has suburb only sorted ascending. This becomes the lookup for my main Table. The Main Table contains..Autonumber as key, Code, Name, Phone and Suburb. This is in a form where you can enter the data. I need the suburb entry to be updated automatically when a new suburb is entered that is not listed instead of having to enter the suburbs table and add them manually. It would be nice to be able to do it on the fly. I thought I could use the "On Not in List" Event and do a procedure call which did a addnew to the suburbs table. It never does the "On Not in List" call as I have a debug.print and nothing gets printed. I thought this would be straight forward but I can not seem to do it and I am new to access programming. I am using Access XP. Any help would be greatly appreciated. Cheers. Steve.

 

[Guest]

Make sure that Limit to List is set to "Yes"

Try this code

Private Sub cmbsuburb_NotInList(NewData As String, Response As Integer)
On Error GoTo handleErr
Dim db As DAO.Database
Dim rs As DAO.Recordset
If MsgBox(" Would you like to add " & NewData & " to the list?", vbQuestion
+ vbOKCancel)= vbOK Then
DoCmd.RunCommand acCmdUndo
Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)
'On Error Resume Next
rs.AddNew
rs!Suburb= NewData
rs.Update
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
rs.Close
Set rs = Nothing
Set db = Nothing
handleErr:
End Sub


Armin_A

Hi, I appologise if this has been asked. I am setting up a simple database that contains a lookup. This loopkup is based on a simple query. Ok let me tell you how it is setup. Table 1 contains 2 fields...autonumber as key and Suburb. A query has been created and has suburb only sorted ascending. This becomes the lookup for my main Table. The Main Table contains..Autonumber as key, Code, Name, Phone and Suburb. This is in a form where you can enter the data. I need the suburb entry to be updated automatically when a new suburb is entered that is not listed instead of having to enter the suburbs table and add them manually. It would be nice to be able to do it on the fly. I thought I could use the "On Not in List" Event and do a procedure call which did a addnew to the suburbs table. It never does the "On Not in List" call as I have a debug.print and nothing gets printed. I thought this would be straight forward but I can not seem to do it and I am new to access

programming. I am using Access XP. Any help would be greatly appreciated. Cheers. Steve.

 

[Steve Smith via AccessMonster.com]

Ok my main problem was I did not have LimitToList set to True - Bloody Help doesnt mention anything about that. It now enters the code which was very similar to yours except:

No Prompting - I want it to add it regardless
No Error Handling
I did not have DAO in front of Database and Recordset.

Now when it activates the procedure call I get "User-defined type not defined" Error message whether I have DAO.Database or just Database? Do I need to declare something? How can I attach my DB so you see it?

Thanks for such a quick response.
Cheers
Steve

 

[Guest]

Hi Steve,

see if you have DAO reference checked. VBA, tools, references and check the
Microsoft DAO 3.6 Object Libary.
Let me know if it works.

Armin

 

[Steve Smith via AccessMonster.com]

Hi Armin,

You have been great so far, Thanks.

Ok, Now it that the DAO is ticked, it works and I do have to have the "DAO" in the front of database and recordset Now it comes up with a Microsoft Information Box saying:

i Microsft Access
The text you entered isn't an item
in the list
Select an item from the list, or enter text
that matches one of the listed items.

I thought it should add it. I changed the setting for LimittoList to No and I do not get this error, but it does not add it. If I have it set to YES it does add it but it does not show up in the list unless I close the form and reopen it and I get the error above again. I also Do not allow Duplicates so I get an error when I try to retype it again. Therefore I assume I need to do a requery on the list or something. I tried it but it does not seem to work. I do not know how to do this on the form data lookup.

Any more thoughts?

Thanks again
Steve

 

[Guest]

Hi Steve,

could you please paste the code you have so I can see it.Have you made any
changes to the code?If you don't want the custom msg in the code , your code
should look something like this:


On Error GoTo handleErr
Dim db As DAO.Database
Dim rs As DAO.Recordset

DoCmd.RunCommand acCmdUndo
Set db = CurrentDb
Set rs = db.OpenRecordset("tblYourTable", dbOpenDynaset)
'On Error Resume Next
rs.AddNew
rs!YourField = NewData
rs.Update
Response = acDataErrAdded

rs.Close
Set rs = Nothing
Set db = Nothing


handleErr:

Let me know

Armin_A

 

[Steve Smith via AccessMonster.com]

Hi Armin,

Here is my code which now works Thanks to you.

Private Sub Suburb_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset

DoCmd.RunCommand acCmdUndo <---- What does this do? Do I Need it?

Set db = CurrentDb
Set rs = db.OpenRecordset("Suburbs", dbOpenDynaset)

With rs
.AddNew
!Suburb = NewData
.Update
.Close
End With

Response = acDataErrAdded <------------ This is what fixed it.

Set rs = Nothing
Set db = Nothing
End Sub

Thanks heaps. I never would have done it without you. I thought it would be simpler with no need for the 2 areas marked <---. Oh well you live and learn.

Thanks once again. Have a Safe and Merry Christmas and New Years.

Cheers

Steve