Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2008
    Posts
    54

    Unanswered: Populate a field with user input

    I have a make table query which makes a temporary table tblTmpCondemned. Since this tblTmpCondemned does not have a date field ,I would like to user to enter the date field, SptDate via a form (frmUserInput) together with the all the fields in the tblTmpCondemned to be appended to a new table (tblCondemned). I cannot group records in tblTmpCondemned if I were to include the date field in making a table .
    So how do I populate the user input date (SptDate) into tblCondemned or tblTmpCondemned whichever is convenient. A set of records for tblTmpCondemned keep changing and after a certain period user enters SptDate via the form. Report can then be based on tblCondemned for certain dates

    Making a tblTmpCondemned:
    SELECT tblFrom.Item, Sum(tblTo.OldQty) AS SumOfOldQty, Sum(tblTo.SptQty) AS SumOfPhyQty INTO tblTmpCondemned
    FROM tblFrom INNER JOIN tblTo ON tblFrom.InvID = tblTo.InvID
    GROUP BY tblFrom.Item;

    tblTmpCondemned should look something like this

    Item SumOfOldQty SumOfSpoiltQty
    A 20 2
    B 40 5
    C 70 3

    The final result in tblCondemned should look like this
    SptDate Item OldSum SpoiltSum
    20/03/2008 A 20 2
    20/03/2008 B 40 5
    20/03/2008 C 70 3

    Please help.
    Last edited by big_mon; 03-21-09 at 11:48.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm sorry but some of your explanations are not clear.

    If the table tblTmpCondemned does not have a date column, as you stated at the beginning of your message, the question of knowing which table is convenient is irrelevant: adding SptDate to tblTmpCondemned is simply impossible without changing its structure.

    Secondly, you do not explain how and when data from tblTmpCondemned is transferred to the new table tblCondemned, nor do you provide any explanation about the tblFrom and tblTo tables and their structures.

    Could you please supply a kind of flowchart or logical sequence of the events in your application.

    With what I know from your explanations so far, I guess that you could create a table with the input dates and find a way to link it to one of the other tables, or if the transmission of data is induced manually when the user enter a date, you could simply add this date (it could be the Now() function so you would not need to bother with getting the date in the form into a query) into your query.

    Finally, the query in your message is a SELECT query, so it cannot be the one that populates the tblCondemned table. What about this one?

    Have a nice day!

  3. #3
    Join Date
    Jan 2008
    Posts
    54
    Thank you for the reply
    Because of complication I will try to explain without adding more unnecessary info which may not affect what I am trying to achieve.
    The only way I can save the sum of old qty and sum of spoilt qty in a subform is to group the items and create a make-table query and create tblTmpCondemned. The sum of old qty and sum of spoilt qty changes everyday in the subform. Therefore the set of records in tblTmpCondemned also changes.
    I would be appending a set of records from tblTmpCondemned to tblCondemned so that these records are stored permanently and I can retrieve them at any time. TblCondemned will need dates when it is appended from tblTmpCondemned so that Access would know which items are old and spoilt on that particular dates.
    I need to make a report every six months on the sum of old qty and sum of spoilt qty of each item which I can retrieve from tblCondemned. Unfortunately my tblTmpCondemned and my tblCondemned do not have dates.
    If tblCondemned has dates, user needs to enter the date or range of dates to retrieve the appropriate records from tblCondemned to produce the report.
    I hope this is clear.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OK, let's try.

    First of all you need a place where to store a date. You can add a Date/Time column in the tblCondemned table or you can create a third table where you store the date. If you chose the latter option this table also have to contains a colum where to store a link to tblCondemned as a foreing key.

    The date you want to store is the one when you append data to the tblCondemned table, so the Now() function will fit here.

    Let's suppose you chose the first option (add a Date/Time column to the tblCondemned table that you name DateOfInsertion). Your append query may looks like:

    Code:
    INSERT INTO tblCondemned ( Col_1, Col_2, Col_3, DateOfInsertion)
    SELECT tblTmpCondemned .Col_1, tblTmpCondemned .Col_2, tblTmpCondemned .Col_3, Now() AS DateOfInsertion
    FROM tblTmpCondemned 
    WHERE <any needed condition>
    Now, every time you run the query, the current date will be inserted into the DateOfInsertion column of the tblCondemned table.

    Does it help?

    Have a nice day!

  5. #5
    Join Date
    Jan 2008
    Posts
    54
    I prefer the user to input the date from say a form instead of extracting the date from the internal clock. How can this be achieve then?
    Thank you

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You cannot do that directly if using a query. Of course you can always write something like:
    Code:
    SELECT Format([forms]![Form_2].[Text_0].[value],"mm/dd/yyyy") AS x
    but it's messy: what if the form is not open ...

    It's better to write a function that returns the value of the input date and use it in the query. The problem of the form being closed remains but you can take care of it with an error handler.

    You can also store the date value from the query into a table then reference this table in your query.

    Have a nice day!

  7. #7
    Join Date
    Jan 2008
    Posts
    54
    You are absolutely right about my difficulty of trying to obtain the date input for appending query using a form. My approach to Access is mediocre.

    Please show me the function code that returns the value of the input date and how do you use it in the query?

    I have not use this method before.
    Thank you for your patience

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OK, let's go!

    We shall first create a public variable that will keep the input date. To do so, create a module, or open an existing one, and in the declaration section, add:
    Code:
    Public UserInputDate As Date
    When I say "a module", it must be an independant module, not a module behind a form: this is very important. As our UserInputDate variable is declared as Public it can be accessed from everywhere in the application.

    In the same module, add a function like this:
    Code:
    Public Function GetUserInputDate()
    
        GetUserInputDate = UserInputDate
        
    End Function
    On the form where the user is supposed to enter a date, create a textbox control, say Text_UserInputDate, set its Format property to a convenient value and add the following code in the AfterUptate event:
    Code:
    Private Sub Text_UserInputDate_AfterUpdate()
    
        UserInputDate = Nz(Me.Text_UserInputDate.Value, UserInputDate)
        
    End Sub
    We use the Nz function to prevent trying to assign a Null value to UserInputDate in case the user removes the contents of Text_UserInputDate (UserInputDate is declared as a Date type variable and Date type variables cannot have a Null value assigned to them: it triggers an error).

    Note that we declared the GetUserInputDate as Public, so it's also accessible everywhere in the application.

    Now the query might look something like this:
    Code:
    INSERT INTO Table2 ( Col_1, Col_2, Col_3, Col_DateTime )
    SELECT Table1.Col_1, Table1.Col_2, Table1.Col_3, GetUserInputDate()  AS DateOfInsertion
    FROM Table1
    WHERE (((Table1.Col_2) Like "data*"));
    I hope this helps.

    Have a nice day!

  9. #9
    Join Date
    Jan 2008
    Posts
    54
    Thanks for your help.

  10. #10
    Join Date
    Jan 2008
    Posts
    54
    It worked perfectly, Sinndho. Well I have learned new things. Thanks for for invaluable help.

  11. #11
    Join Date
    Jan 2008
    Posts
    54
    Calling Sinnho.
    The date generated in the tblCondemned is a text instead of date/time. How do you change data type of the DateOfInsertion to date/time so that I can use generate a query based of real date?
    Thanks

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use a conversion function such as:
    Code:
    CDate(DateOfInsertion)
    or you could strongly type the GetUserInputDate function (but this one should already return a variant of type date as the underlying variable UserInputDate is declared as a date):
    Code:
    Public Function GetUserInputDate() As Date
    Have a nice day!

  13. #13
    Join Date
    Jan 2008
    Posts
    54
    Great! By changing the funtion to Public Function GetUserInputDate() As Date it works.
    Thank you very much.

Posting Permissions

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