Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    42

    Unanswered: Form to a Table - Help Please!

    I have a form that writes data to a table, but I also want it to write some data to a second table that is not the data source for the form. Is there a way to do that?

    Further explaination:
    On the form, the user picks a day, and all the time slots are shown. Some are "available" and others are not. The user picks which time slot they want. One table (the data source) contains all the days and time slots, and records which ones were picked. In the other table, I want to write which user picked what time slot (then I have e-mails automatically sent, etc).

    Can anyone help me please?
    Thanks,
    Noobie

  2. #2
    Join Date
    Sep 2003
    Posts
    228
    you can have a subfrom who's data source is your second table...
    or
    you can use VB code to write an update query to update your second table with the values entered into your form.

  3. #3
    Join Date
    Feb 2004
    Posts
    42
    Ok, I figured out that you can...now I need to know how. What would be the syntax for the second option of writing VB code?

    Thanks,
    Noobie

  4. #4
    Join Date
    Sep 2003
    Posts
    228
    To update an existing record:
    Dim Who as string
    dim SQLStr as string
    dim insrt as string

    SQLStr = "UPDATE [Table] SET [Field1] = """ & Forms![MyForm].txtBox.Value & """" & " WHERE [Field2] = """ & Who & """"
    DoCmd.RunSQL SQLStr

    To insert a new record:

    insrt = "INSERT INTO [Table]([Field1],[Field2]) VALUES (""" & _
    Who & """, " & True & ");"
    DoCmd.RunSQL insrt

    Hope that helps.
    You would need to put this code in an event. Which if event depends on when you want the data to be entered into the table. Look in the Access help files for events to determine which one you want to use.

  5. #5
    Join Date
    Feb 2004
    Posts
    42
    I can't figure out what I'm doing wrong. I think it is the syntax of the constants and variables that is screwing me up.

    I'm tyring to change and numbers that are a 3 to a 2. The table name is tblDates and the field name is 0000.

    Here is what I have that is not working:

    DoCmd.RunSQL "UPDATE tblDates SET tblDates.[0000] = 0 " &_
    " WHERE (((tblDates.[0000]) = 3 ))"

    Any ideas? Also, is there a list somewhere of what syntax everything should be in?
    ie:
    variable string '" & var & "'
    date #" & var & "#
    constant string ______
    constant integer ______
    Thanks,
    Noobie

  6. #6
    Join Date
    Sep 2003
    Posts
    228
    Do you know how to design a query in design view? You can design an update query in design view, change the view to SQL and then copy and paste the text of the query to you VB code.

    Not knowing data types of your fields, here is my guess of the syntax:
    DoCmd.RunSQL "UPDATE tblDates SET tblDates.[0000] = 0 " & _
    "WHERE tblDates.[0000] = 3"

Posting Permissions

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