Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,

    I'm got a form that has cmdClosePO on it. The form is bound to table shipping/invoice. When the user clicks the button they want [closed] which has yes/no format, in purchase maked as true. The way to identify that the correct record is being marked is the [po order] in purchases =txtpo on the receiving form. I'm not sure how to code this.

    Thanks!
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can run an SQL statement to update the purchase table:

    Dim strSQL As String
    strSQL = "UPDATE Purchase SET Closed = True WHERE [po order] = " & Me.txtPO
    CurrentDb.Execute strSQL, dbFailOnError

    This assumes that po order is a number field. If it is a text field, change the line that sets strSQL as follows:

    strSQL = "UPDATE Purchase SET Closed = True WHERE [po order] = " & Chr(34) & Me.txtPO & Chr(34)

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='Leesha' post='798655' date='18-Oct-2009 16:57']Hi,

    I'm got a form that has cmdClosePO on it. The form is bound to table shipping/invoice. When the user clicks the button they want [closed] which has yes/no format, in purchase maked as true. The way to identify that the correct record is being marked is the [po order] in purchases =txtpo on the receiving form. I'm not sure how to code this.

    Thanks!
    Leesha[/quote]

    There's going to be a variety of possible answers to this so I am only going to give one possible option.
    You may have to change field names to suite your application.

    But assuming Purchase Order Table is called Purchases and [closed] is the yes no field, then something like this in the
    Buttons on Click Event

    Note I have not added any error trapping to this which really you ought to do.

    Dim dbs as Dao.Database, rst As Dao.Recordset

    'Check they meant it
    If msgbox("Do you really want to close this Order",vbYesNo+vbQuestion+vbDefaultButton1,"Close PO")=vbNo then Exit Sub

    'Check OK with valid PO Number
    If txtPO & "" <> "" Then
    set dbs=CurrentDB
    set rst=dbs.OpenRecordset("Select * From PURCHASE Where [po order]=" & txtPO)
    If not rst.eof then
    rst.edit
    rst![closed]=True
    rst.Update
    Endif
    rst.close
    dbs.close
    set rst=Nothing
    set dbs=Nothing
    msgbox "Purchase Order Closed",vbExclamation
    Endif




    Please note that in the above if the Purchase Order No is Not Numeric but alpha numeric then the Open Recordset line should read

    set rst=dbs.OpenRecordset("Select * From PURCHASE Where [po order]='" & txtPO & "'")

    As I say one method of many
    Andrew

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='798657' date='18-Oct-2009 12:12']You can run an SQL statement to update the purchase table:

    Dim strSQL As String
    strSQL = "UPDATE Purchase SET Closed = True WHERE [po order] = " & Me.txtPO
    CurrentDb.Execute strSQL, dbFailOnError

    This assumes that po order is a number field. If it is a text field, change the line that sets strSQL as follows:

    strSQL = "UPDATE Purchase SET Closed = True WHERE [po order] = " & Chr(34) & Me.txtPO & Chr(34)[/quote]

    Hi Hans,

    PO Order is an autonumber. I'm not sure is the makes a difference or not. I used the first part of the code that you gave me, making one change. The table is called "Purchases". I had left out an "s". Anyway, I get an error that says "Too few parameters. Expected 1". I'm not sure what its looking for or if the autofield makes a difference.

    Thanks,
    Leesha

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Also make sure that the names closed and po order are spelled EXACTLY as in the table design.

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='HansV' post='798660' date='18-Oct-2009 17:47']Also make sure that the names closed and po order are spelled EXACTLY as in the table design.[/quote]

    I think you need

    strSQL = "UPDATE Purchases SET Closed = True WHERE [po order] = " & Me.txtPO

    Since PO is a number field it does not need the " chr(34)
    Andrew

Posting Permissions

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