Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Londo, UK
    Posts
    20

    Unanswered: Form changing data in table not query

    I have a databse with three tables, tblName, tblBook and tblOrder and a query based on these tables. All the reports and data output are based on this query.

    I have a form for data entry and cancellations. The form is based on the query. There is a combo box which selects the data from tblBook. There is a 'Cancelled' YES/NO button which has the collowing code:-

    Private Sub Cancelled_AfterUpdate()
    If Me.Cancelled = -1 Then
    Me.Combo24.Value = Null
    Me.Price.Value = Null
    Me.Frequency.Value = Null
    Me.No.Value = Null
    Else
    Me.Combo24.Enabled = True

    End If
    End Sub

    This is OK in blanking the textboxes on the form, but it also blanks the fields in the tblBook, which I do not want to happen, as this is a static reference table.

    Ideally I would lke only the query to be altered not the underlying table, or perhaps only the Order table.

    I have tried altering the ControlSource of the ComboBox to the query but it does not like that. Is there another value I could use instead of NULL?

    Any help you can offer would be appreciated.
    Janet

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    ok well - fundamentally a query returns records from tables... when you see query data you are actually seeing the table data itself ...and thus if you change that data - - you are changing the table data. so that is that. there is no way around that.

    now some specific issues you mention: "...but it also blanks the fields in the tblBook, which I do not want to happen..." - - while you don't mention which fields it would seem the obvious answer is to remove the line of code that is causing it to change to null.

    you can also use this: Me.Price = ""

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You simply can't do that the way you describe it. A query does not contain date "per se"; A select query is just a view of the data that reside in it's underlying table.

    For a better understanding of the concept, see:

    http://office.microsoft.com/en-us/ac...139541033.aspx
    Microsoft Access tips: What are these objects?
    Crystal's Access Basics
    Have a nice day!

  4. #4
    Join Date
    Aug 2003
    Location
    Londo, UK
    Posts
    20
    OK. I see I am going the wrong way round this.
    The three tables are tblName - contains the names of subscribers, and a YES/No Cancelled field
    tblBook - contains the name of the book, price and frequency per year.
    tblOrder - contains the ID of the book requested for the NameID from tblName., and the number ordered. The qryBook contains all this detail.

    What I want to do on the form is when the Cancelled button is clicked the textboxes which give the details (BookName, price and Frequency which are from tblBook) to be blanked and the tblOrder to be changed so that the bookId, and BookNo are removed). I do not want the tblBook to be changed.

    The cancelled button has this code:
    Private Sub Cancelled_AfterUpdate()
    If Me.Cancelled = -1 Then
    Me.Combo24.Value = Null
    Me.Price.Value = Null
    Me.Frequency.Value = Null
    Me.No.Value = Null
    Else
    Me.Combo24.Enabled = True

    End If
    End Sub

    The combox24 has this select code:
    SELECT tblBook.BookID, tblBook.Book, tblBook.Price, tblBook.Frequency
    FROM tblBook
    ORDER BY tblBook.Book;

    and the AfterUpdate code:

    Private Sub Combo24_AfterUpdate()
    Me.Price = Me.Combo24.Column(2)
    Me.Frequency = Me.Combo24.Column(3)
    End Sub

    I think changing Null to "" will have the same effect - i.e. change the tblBook.

    Is it possible to dowhat I outlined in paragraph 2?

    Many thanks for any help you can give me.
    Janet

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is what I came up to. I did not use the table tblName: you mention it but it does not appear in the logic you describe.

    My data definition (metadata) is as follows:

    tblBook:

    -BookID: Long Integer
    -Book: Text(50)
    -Price: Currency
    -Frequency: Text(50)

    tblOrder:

    -OrderID: Long Integer
    -BookID: Long Integer
    -NumberOrdered: Long Integer

    qryBook SQL:
    Code:
    SELECT tblBook.BookID,tblBook.Book, tblBook.Price, tblBook.Frequency, tblOrder.OrderID, tblOrder.NumberOrdered			
    FROM tblBook LEFT JOIN tblOrder ON tblBook.BookID=tblOrder.BookID
    The form is named: frmBook and its RecordSource is: qryBook
    All TextBox controls have the name of their ControlSource property, prefixed with "Text_" (i.e. Text_Book for the field Book, etc.).

    Here is the code:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Enum Col
        BookID = 0
        Book = 1
        Price = 2
        Frequency = 3
    End Enum
    
    Private Sub Cancelled_Click()
    
        Dim strSQL As String
        Dim lngID As Long
        
        lngID = Me!BookID
        strSQL = "DELETE FROM TblOrder WHERE BookID = " & Me!BookID
        CurrentDb.Execute strSQL
        Me.Requery
        MoveToRecord lngID
        
    End Sub
    
    Private Sub Combo24_AfterUpdate()
    
        MoveToRecord Me.Combo24.Column(Col.BookID)
        
    End Sub
    
    Private Sub Form_Current()
    
        If IsNull(Me!OrderID) Then
            Me.Text_Price.ControlSource = ""
            Me.Text_Frequency.ControlSource = ""
            Me.Text_Book.ControlSource = ""
        Else
            Me.Text_Price.ControlSource = "Price"
            Me.Text_Frequency.ControlSource = "Frequency"
            Me.Text_Book.ControlSource = "Book"
        End If
        Me.Combo24.Value = Me!Book
        
    End Sub
    
    Private Function MoveToRecord(ByVal RecordID As Long) As Long
    
        Dim rst As dao.Recordset
        Dim strCriteria As String
    
        On Error GoTo Err_MoveToRecord
    
        strCriteria = "BookID = " & RecordID
        Set rst = Me.RecordsetClone
        rst.FindFirst strCriteria
        If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
        rst.Close
    
    Exit_MoveToRecord:
        Set rst = Nothing
        Exit Function
    
    Err_MoveToRecord:
        MoveToRecord = Err.Number
        Err.Clear
        Resume Exit_MoveToRecord
        
    End Function
    Have a nice day!

  6. #6
    Join Date
    Aug 2003
    Location
    Londo, UK
    Posts
    20
    Hi,
    Thanks for that. I have had a quick look at it. Unfortunately I shall not be able to do anything much more until Monday.

    But just a few quick points - the table tblName is used

    The format is

    NameID - Autonumber
    Fname - Text - 20
    Surname - Text 25
    TelNo - Text - 1-
    Cancelled YES/NO

    The SQL for the sqlBook is:

    SELECT tblBook.BookID AS tblBook_BookID, tblBook.Book, tblBook.Price, tblBook.Frequency, tblName.NameID AS tblName_NameID, tblName.Fname, tblName.Surname, tblName.TelNo, tblOrder.ID, tblOrder.NameID AS tblOrder_NameID, tblOrder.[No], tblOrder.BookID, tblName.Cancelled
    FROM (tblOrder INNER JOIN tblBook ON tblOrder.BookID = tblBook.BookID) INNER JOIN tblName ON tblOrder.NameID = tblName.NameID
    ORDER BY tblName.Surname;

    (The inner join was the result of a previous post, see "Missing records in query"

    I need the names in the form because this pulls the information for that individual.

    I don't really want to lose the OrderID because this is the order for the individual. Each person has usually only one order, and though it may be cancelled I would like to reuse this order if they re-subscribe at a later date. Or is this making things too complicated.

    Posted in haste.

    Many thanks for your help.
    Janet

Posting Permissions

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