Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add and delete records via button from listbox (2002 -SP2)

    I need to add and delete records from a table, via code, from a button. (So that i can then RECORD the event taking place in another table)

    So far, i have set up the form with listboxes inside it, to select the correct record from the table, and tried some code found via search to add a record. which is giving me an error.

    run time error 3001. "Arguements are of the wrong type, are out of acceptable range, or are in conflict with one another'

    The code attached to the button, which is giving the error, you can see in the picture.

    So the process is, you do a search from the right hand listbox (field35) to select the person you want, and that persons MEMOID field, (short name so to speak) is placed in both Text43 field, and Text11 (top left of screen)

    That persons records then get populated (from TWORKAREAS2 table) in listbox 'list34'

    In this example you can see 'Steve Wilders' is assigned to an area of 'Line 1 Depal' only. so, the user would then select another location from Combo50 at the bottom, and that will add that location to the table TWORKAREAS2. (and record who did it, and when)
    Similarly, i'd like to select a record from that list34 listbox, and delete it via a button. (and again record the event happening in another table)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add and delete records via button from listbox (2002 -SP2)

    On which line do you get the Error 3001?

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add and delete records via button from listbox (2002 -SP2)

    On the Rst.Open.............. line Hans

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add and delete records via button from listbox (2002 -SP2)

    There should be a comma after cnn in that line, not a point. adOpenKeyset is the 3rd argument of rst.Open, not a property of cnn.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add and delete records via button from listbox (2002 -SP2)

    That seems to update now Hans. however, i can't seem to make the Workarea update correctly from the combo Box.

    Combo50 rowsource = <font color=red>SELECT wa.wa, wa.id FROM wa;red</font color=red>

    I'm trying to use Dlookup to put the right value in.

    <font color=red>'-------------------- Add a record --------------------------------
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim N1 As String, N2 As Integer, N3 As Date

    Set cnn = CurrentProject.Connection

    N1 = Forms![F2]![Text11]
    'N2 = DLookup("Workarea", "wa", "[Combo50]= " & Chr(34) & "id" & Chr(34))
    'N2 = 5
    N3 = Date

    rst.Open "TWORKAREAS2", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    rst.AddNew

    rst!MEMOID = N1
    rst!Workarea = N2
    rst!wdate = N3

    rst.Update
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing</font color=red>

    The field in the table is set to a number, (but shows in the table as text, guess thats the lookup working in the table) but, if i set it to a numeric value, thats all i get.
    Here is a screenie of the tables definition

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add and delete records via button from listbox (2002 -SP2)

    The row source of Combo50 should be the same as that of WorkArea in your screenshot. You don't need DLookup then, you can just set

    N2 = Me.Combo50

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add and delete records via button from listbox (2002 -SP2)

    That gives a type mismatch Hans.

    Combo50 is text. rst!Workarea is numeric.

    I think i have to lookup the numeric number of the text in combo50. from the wa table.

    Which i *thought* was something like this:

    <font color=blue>N2 = DLookup("id", "wa", "[Combo50]= " & Chr(34) & "Workarea" & Chr(34))</font color=blue>

    But this doesn't work.

    Gives 'Run Time error 94, invalid use of NULL'

    (Dlookup Criteria... argggg)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add and delete records via button from listbox (2002 -SP2)

    The simplified syntax for DLookup is DLookup("field1", "table_or_query", "field2 = " ...)
    Combo50 is not a field in the wa table (nor is WorkArea) so your DLookup won't work.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add and delete records via button from listbox (2002 -SP2)

    Will play with this a bit Hans and see how it goes.

    The problem with stripping the db down, is that its huge, but will go that route if i can't get it to work. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>



    EDIT: what works, is <font color=blue>N2 = DLookup("id", "wa", "[wa]= [Combo50]")</font color=blue>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add and delete records via button from listbox (2002 -SP2)

    Again, set the Row Source of Combo50 to

    SELECT wa.id, wa.wa FROM wa

    Set the Column Widths property to 0";1" or something similar. You should be able to use N2 = Me.Combo50 then.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •