Re: Form/Subform

On Sun, 2 Mar 2008 13:45:50 +0900, "Allen Browne"
<AllenBrowne@xxxxxxxxxxxxxx> wrote:

I tested this:

Private Sub Fullname_NotInList(NewData As String, Response As Integer)
Dim sql As String
Dim strNames() As String

strNames = Split(NewData, ",")
sql = "insert into Employees ([Last Name],[First Name]) values ('"
& Trim$(strNames(0)) & "','" & Trim$(strNames(1)) & "')"
DoCmd.RunSQL sql, False
Response = acDataErrAdded
End Sub

Note: I wrote the in-line sql for brevity; normally I would us a
parameterized append query. Also a "Are you sure you want to add..."
messagebox would be appropriate.

-Tom.



"Tom van Stiphout" <no.spam.tom7744@xxxxxxx> wrote in message
news:0qaks398r1h9vvf9uqt9j65q7b07p5sm74@xxxxxxxxxx
On Sun, 2 Mar 2008 13:11:56 +0900, "Allen Browne"
<AllenBrowne@xxxxxxxxxxxxxx> wrote:

Presumably records are added using dropdowns.
You can add new records if you implement the NotInList event.
-Tom.

Hi Tom. Do you have suggestions on how to use NotInList for the case the OP
has in mind?

For example, the Club form has a subform for the persons associated with the
club. Say the subform contains a PersonID combo, with a RowSource of:
SELECT PersonID, Surname & ", " & FirstName AS FullName
FROM tblPerson
ORDER BY Surname, FirstName;
with the first column zero-width.

User types in:
Smith, Fred
which is not in the Person table yet.
Can you use the NotInList to add this to the Person table?

(To the original poster: If you are using Access 2007, you could use the
combo's ListItemsEditForm property.)
.

Relevant Pages

  • Re: Import Access records to excel (parameter is a called funct)
    ... It querys alright but does not roll back ... > Dim SQL As String ...
    (microsoft.public.excel.programming)
  • Re: recordset.clone
    ... Dim SQL As String ... moment i open my form with the subform it applieas already the filters... ...
    (microsoft.public.access.formscoding)
  • Re: Form/Subform
    ... Dim sql As String ... Dim strNames() As String ... DoCmd.RunSQL sql, False ...
    (comp.databases.ms-access)
  • ExecuteNonQuery Wierdness
    ... Public Sub Write(ByVal sensor As Integer, ByVal eventId As Integer, ... ByVal eventValue As String) ... Dim sql As String ...
    (microsoft.public.dotnet.languages.vb)
  • RE: ExecuteNonQuery Wierdness
    ... Public Sub Write(ByVal sensor As Integer, ByVal eventId As Integer, ... ByVal eventValue As String) ... Dim sql As String ...
    (microsoft.public.dotnet.languages.vb)