Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    12

    Unanswered: Referencing form in sql query

    Hi
    I have an update query i have written in sql, but he table to update is typed in from a form in a text box.
    How can i code the query so it updates the table as input in the form?

    Its the first part of UPDATE i need the syntax for?

    thanks

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You could link the form to the table (creating a bound form) - set the Forms Recordsource to the name of the table and make sure each control on the form has the controlsource set to the underlying field.

    If you have to use an unbound form then you can run the SQL in the forms AfterUpdate event.

    See Access Help on form events and recordsources

    Justin

  3. #3
    Join Date
    Nov 2004
    Posts
    12
    Thanks

    Its more that the user will dump a new table into the db, the form will give the update query the name of this new table to update.

    At present the query will run but doesnt know what table to run on, so i need to dynamically change the query based on the input from the text box.
    The text box itself has no relationship with the table, except the name of it.

    cheers

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    To change the SQL use the following:

    Dim strSQL as String

    strSQL = "UPDATE " & Me("MyTextFieldWithTableName").Value & " SET MyFieldNames = MyNewValue"

    DoCmd.RunSQL strSQL

    If you placed this VBA under a Click event of a button on the form
    Justin

  5. #5
    Join Date
    Nov 2004
    Posts
    12
    cheers!

    Is it possible to amend the sql code of the update query itself to reference a form?

    the code is
    UPDATE [DONT KNOW NAME!], [IDN Switching]

    SET [A1 Broken].[idn 1] = [IDN Switching].[Majority Holder]

    WHERE ((([1].[idn 1]) Is Null) And (([1].idn) Like ([IDN Switching].[Post Outcode] & "*")));

  6. #6
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    It is possible for the update query to reference the text field on the form itself without using VBA. You would use the following in the query designer:

    UPDATE Forms!NameOfFormUserUpdates!NameOfFieldUserUpdates SET etc...

    It is quite fussy about the syntax you use so check the Help file for the correct syntaxt. Also the query can only be run when the form is open and a value has been entered in the text box.

    I would use the VBA method as you have more control and can trap for errors

    Justin

  7. #7
    Join Date
    Nov 2004
    Posts
    12
    thanks for that

    not too good in vb so prefer just to wing it in sql...

    the syntax seems ok, but it refuses to work. UPDATE SYNTAX INCORRECT?

    UPDATE Forms!frmtxt!txtbox, [IDN Switching] SET Forms!frmtxt!txtbox.Idn1 = [IDN Switching].[Majority Holder]

    WHERE ((([ Forms!frmtxt!txtbox].[Idn1]) Is Null) And (([ Forms!frmtxt!txtbox].[Idn]) Like ([IDN Switching].[Post Outcode] & "*")));

  8. #8
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    The SQL is wrong for a number of reasons.

    UPDATE Forms!frmtxt!txtbox, [IDN Switching] - You can only update one table at a time (the comma denotes a separate table - use FROM to join tables together)

    SET Forms!frmtxt!txtbox.Idn1 = [IDN Switching].[Majority Holder] - in the Query desinger you can only reference a form for the value, not a field name. VBA is by far the better solution, the code i gave you is the basic outline of what you need:

    Dim strSQL as String

    strSQL = "UPDATE " & Me("MyTextFieldWithTableName").Value & " SET MyFieldNames = MyNewValue"

    DoCmd.RunSQL strSQL

Posting Permissions

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