Add date and team ID when using Insert Into query

[megatronixs]

Hi all,

I´m using a query to insert new cases into the main table. I use a table where we have some cases to be assigned to people. in the form that holds the subform in data sheet view, there is a combo box where the persons name is selected to whom the case will be assigned. I use a function to get the login from the pc so I can get the name of the person and the "team ID".
I can get almost all the fields that are in the assign table to the main table, I just can´t get the date and the team id into the main table as the those 2 fields are not the the assign table.
How can I pass the value of "team_id" and the date into the main table fields "team_id" and "request_date" ?

Greetings.

(I will try later in the evening to make a dummy database so it will be easier to see what I mean)

 

[RuralGuy]

Have you looked at the OpenArgs argument of the OpenForm command?

 

[jdraw]

mega,
Perhaps you could show us your tables and relationships so we can see the underlying structures.

 

[megatronixs]

Hi all,

This is the function I have to call for the team_id

Function GetUserTeamID() As String
GetUserTeamID = DLookup("team_id", "tbl_Users", "main_login = " & _
    Chr$(34) & GetUserName & Chr$(34))

End Function

and this is the Insert Into SQL:

Private Sub btn_assign_new_cases_Click()
    Dim team_id As Integer
    Dim strSQLApp As String
    Dim strSQLDel As String
strSQLApp = "INSERT INTO tbl_main_data (Client_Name, Worked_On_By, analyst_name, status, Activity_Date, country) SELECT Client_Name, Worked_On_By, analyst_name, status, Activity_Date, country FROM tbl_new_data WHERE [analyst_name] <>'Unassigned'"
strSQLDel = "DELETE Client_Name, Worked_On_By, analyst_name, status, Activity_Date, country FROM tbl_new_data WHERE [analyst_name] <>'Unassigned'"
    DoCmd.RunSQL strSQLApp
        DoCmd.RunSQL strSQLDel
            MsgBox "The Record has been moved"
Me.tbl_new_data.Requery
End Sub

As you can see, in the temp table "tbl_new_data" I don´t have the "request_date" and the "team_id", but I do have them on the "tbl_main_data"

the temp table "tbl_new_data gets data from a excel sheet we receive to insert the new cases and then to assing them.

Greetings.

 

[arnelgp]

why not put team_id on a Tempvars:

' create tempvars "team_id" if not in tempvars collection
If Isnull(Tempvars("Team_ID")) Then Temvars.Add "Team_ID", 0
' assign new value to Tempvars("Team_ID")
Tempvars("Team_ID") = GetUserTeamID()

now you can use this variable application wide.
and then back to your sql:

strSQLApp = "INSERT INTO tbl_main_data (Team_id, Request_Date, Client_Name, Worked_On_By, analyst_name, status, Activity_Date, country) SELECT " & Tempvars("Team_ID") & ", " & Date() & ", Client_Name, Worked_On_By, analyst_name, status, Activity_Date, country FROM tbl_new_data WHERE [analyst_name] <>'Unassigned'"

 

[megatronixs]

Hi, I was absent and had no time to check it out, so I will do now

Greetings.

 

[megatronixs]

Hi, just tested, but it seems that it is a function from access 2007 and up.
So, I will not be able to use it :-(

Greetings.

 

[cyanidem]

OpenArgs should work, as RuralGuy suggested.
You could pass both as one delimited string, then split it back in two variables in OnOpen event.

 

[RuralGuy]

Here's a link that uses this technique: http://www.baldyweb.com/OpenArgs.htm

 

[megatronixs]

Hi,
I was reading about it, but I´m thinkning how to use this to pass it to a query.
I just need to pass the team_id into the Insert Into query to pass it to the table.

greetings.

 

[megatronixs]

hi RuralGuy, I just see your post now.
I will try to read it and see if I get it

Greetings.

 

[megatronixs]

hi all,

At the end I came up with a simple solution, I just added in the temp table one more field, that field was filled in when I use the combo box to select a user and puts the team_id in the field. So, when the query runs, it does the job good and all fields are filled in.

Thanks all for your help, and I could learn something new with those links.

Greetings.

 

[RuralGuy]

Thanks for posting back with your success and solution. Have you marked this thread as Solved yet?