help on subform and textbox

[Jeffrey Tan]

I am having some trouble with a subform.

I have a form that contains a subform about problem tracking. The subform contains the history of the problem as well as when the ticket was created for the problem.

recently, i added a textbox to the from which simply grabs the user that opened the ticket.

the button to create a new ticket contains code that automatically grabs the username of the computer and writes it to the subform with the following information:
user_name | date | "Ticket Created"

so now whenever a user clicks the new ticket button, it populates the subform as well as the new textbox.

as for the old problem tickets that do not show the user who created the ticket, is there any way to take the first username (first cell from first row) from the subform and copy it over to the textbox?

i hope i explained my problem clearly.

thanks!

[NeoPa]

Jeffrey:
i hope i explained my problem clearly.

I'm afraid most of what you say contradicts the rest of it, so it could be clearer. Let me see if I can take a stab at guessing what you meant to say though. You tell me if I'm anywhere near the mark.

You had a database which included a form and a subform connected by (Some problem identification). Previously, the subform contained controls to reflect both the date and the Ticket Information. This latter control possibly contains free-text describing some update or other to the situation pertaining to the problem. At this point there is no User_Name info maintained. At this point also the Command Button to create a new ticket sets values for the date as well as the Ticket Information only.

Then a new control was added to the subform called [User_Name]. The Command Button was updated to add this new information automatically by grabbing it from the computer.

All well and good, but the pre-existing records, those created before this latest change, have no values for this new control. You want to know how best to remedy this situation, and suggest that duplicating the first row might make sense.

Please let us know if that is an accurate reflection of your question.

[Jeffrey Tan]

Hi NeoPa,

your interpretation of my problem is close, but not exactly. you mentioned it's the new field i added for the subform, but it's actually, the new field i added for the main form. the username in subform is already there. i would like to copy the PREVIOUS usernames in the subform (located in row one and cell one) and put them individually into the new textfield on the main form.


------

Let me rephrase my problem.

I have a form that is connected to table 'primary'
i have a subform in that form that is connected to the table 'secondary'

recently, i added a new field in 'primary' that is called opened_by (text field)

i put that field onto my form and when the user clicks on 'new ticket' it puts their username into that field.

Now this only happens when the user clicks 'new ticket'. The usernames for the previous tickets created are not in the table since this new field was added recently.

In the subform, the field list contains the following:
1. username (query from a username table)
2. date
3. notes

Whenever the main form is opened and the user clicks 'new ticket', the username is populated into the username field of the subform.
so for example
1) i click 'new ticket'
2) username for subform is automatically added (subform)
username is also added to opened_by textfield (primary form)
3) today's date is added
4) the words "Ticket Created" is automatically added

Now what I want to do is the following
1) take the username from PREVIOUS tickets (which would be from the first row and the first cell of the SUBFORM)
2) copy that and put it into the username textbox in the main form (The one recently created)

[Rabbit]

Use a subquery to select the top 1 username ordering by the date and then use that to update the primary table.

[Jeffrey Tan]

@Rabbit:

Can you give me an example sql statement?

Thanks.

[Rabbit]

UPDATE someTable
SET someField = (
   SELECT TOP 1 someField
   FROM otherTable
   WHERE someTable.ID = otherTable.ID
   ORDER by otherField
   )

[Jeffrey Tan]

hmm..somehow i'm getting an error:

UPDATE user_problem_log 
SET opened_by = ( 
   SELECT TOP 1 user 
   FROM usr_problem_list 
   WHERE usr_problem_list.trouble_no = user_problem_log.trouble_no 
   ORDER by date
   ) 
 

error: operation must be an updatable query.

and also, in my usr_problem_list table (the one that links to subform), there may be rows that have the same trouble # (meaning that in the subform, 3 rows were all related to that trouble_no)

this won't be a problem right?

or do i have to use some kind of SELECT DISTINCT TOP 1 if there is such thing.

[Rabbit]

Looks like you won't be able to use a subquery in the update, try a join instead.

[NeoPa]

So, it seems you want to reflect the username of the first Ticket (I believe you call this table [Usr_Problem_List]) on each problem record (I believe you call this table [User_Problem_Log]). This would be straightforward enough, except you have existing records where this data has not already been reflected. It's always been there for Tickets, but not for the Problems.

I would say there are two issues to deal with here :

1. How to update the existing [User_Problem_Log] records to set [Opened_By] to reflect the [User] of the first matching record of [Usr_Problem_List].
   
   This is where Rabbit was assisting with his UPDATE SQL code. I suspect what is required is a JOIN between the two tables on matching records with a filter to exclude all such matching records that have other matching records from the [Usr_Problem_List] table with earlier dates. Without the fields to use in the JOIN though we can't do the SQL for you.
   
   It should be easy enough to do in the Query Design Grid though. See what you can come up with, and if you can't get it to work then post the SQL you're trying. That should at least indicate what it is we're supposed to be working with.
2. How to ensure, on an ongoing basis, that only the first Usr_Problem_List.User value entered is ever used to update User_Problem_Log.Opened_By.
   
   NB. I will assume for now that updates to the first Usr_Problem_List.User need not be reflect in User_Problem_Log.Opened_By if/when changed.
   
   This is easier to handle. The code to update the control on your main form that's bound to User_Problem_Log.Opened_By (after Usr_Problem_List.User is entered) need only check that the current value is Null before updating. If the value is not Null then it's been done before and the new value is either not the first record to be added, or it is, but it's a later update.