Update Multiple Records at once 2

[ba4crm]

Hello,
I have a table that has subscriber ids, where each subscriber can have 1 or more than 1 errors. The errors are listed as individual records on this table. Each subscriber belongs to a customer number (they can only belong to one customer number) I am displaying the subscriber numbers under each customer number and would like the users to select more than one subscriber number and assign the subscriber records to themselves. Ideally, I would like the users to define how many subscriber records they would like to select, but I would settle for creating a pre-set number of records (5, 10, etc). When they select the subscriber ids, all the associated error records should be assigned to the user.
How can I do this? I am using Access 2003.

 

[ba4crm]

Here is the code that I am using.

Option Compare Database

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

 

[Andrzejek]

Looks like your function fOSUserName is not returning the user name. Try:
[tt]
Debug.Print fOSUserName

strSQL = "UPDATE Table1 " & vbNewLine _
& " Set Assignee = '" & fOSUserName & "' " & vbNewLine _
& " WHERE Table1.SubId IN(" & strCriteria & ") "
[/tt]
Set the breake point on Debug statement and step thru your code and see what's going on.

You may also try just:
[tt]
strSQL = "UPDATE Table1 " & vbNewLine _
& " Set Assignee = '" & [blue]Environ("USERNAME")[/blue] & "'" & vbNewLine _
& " WHERE Table1.SubId IN(" & strCriteria & ") "
[/tt]

Have fun.

---- Andy

 

[ba4crm]

Oh my goodness that worked!!!!!
Andy - I cannot thank you enough for the guidance and for the patience.

You are a true gem!

 

[Andrzejek]

Which one worked? Your fOSUserName (which should work) or the new (Environ("USERNAME")) which some people look down upon?

Have fun.

---- Andy

 

[ba4crm]

Hi Andy - the (Environ("USERNAME")) worked.
I do have one more question though.

Here is my current update SQL code:
strSQL = "UPDATE ERM_FallOut " & vbNewLine _
& " Set Assigned = '" & Environ("USERNAME") & "' " & vbNewLine _
& " WHERE ERM_FallOut.SubId IN(" & strCriteria & ") "

I would like to add one more criteria of Date (ERM_FallOut.Date) to the update query.
Date will be a value in the same form called "Date"

Thanks

 

[dhookom]

You should avoid giving your fields/controls names of reserved words. Date is a function so it shouldn't be a field name.

strSQL = "UPDATE ERM_FallOut " & vbNewLine _
    & " Set Assigned = '" & Environ("USERNAME") & "' " & vbNewLine _
    & " WHERE ERM_FallOut.SubId IN(" & strCriteria & ") AND [Date]=#" & _
    Me.[Date] & "#"

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

I am with dhookom - do NOT use reserved words as control names, or field names in your database. Date, Name, Time, Select, Case, etc. should be something like Hire_Date or Due_Date, Last_Name, Quit_Time, Select_Person, Case_No, etc. Also, in field names in your data base I would avoid Spaces, use underscore _ instead.

Just FYI

Have fun.

---- Andy

 

[ba4crm]

I took Dhookum's advice and replaced the field "Date" with "RunDate". I am trying to use the RunDate and another text field "TPID" in the update criteria. Both fields are text fields in the same table. Here is my current code:

Private Sub cmdAssign_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim fOSUserName As String
Dim strRunDate As String
Dim strTP As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryAssignSubs")

strRunDate = Forms!frm_ListofCerts!RunDate
strTP = Forms!frm_ListofCerts!TPID
DoCmd.SetWarnings False

For Each varItem In Me!ListSubs.ItemsSelected
strCriteria = strCriteria & ",'" & Me!ListSubs.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"

Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "UPDATE ERM_FallOut " & vbNewLine _
& " Set Assigned = '" & Environ("USERNAME") & "' " & vbNewLine _
& " WHERE ERM_FallOut.SubId IN(" & strCriteria & ") AND [RunDate]= Me.RunDate AND [TPID] = Me.TPID "

qdf.SQL = strSQL

DoCmd.OpenQuery "qryAssignSubs"
Debug.Print strSQL

DoCmd.Close acForm, "frm_MainMenu"
DoCmd.OpenForm "frm_MainMenu", acNormal

Set db = Nothing
Set qdf = Nothing

DoCmd.Close acForm, "frm_ListofCerts"

End Sub

However, when I run the code, it prompts me for the parameter value of RunDate and TPID. How should I modify the code to allow the two additional criteria?
Thanks for your help

 

[dhookom]

ba4crm,
You have Debug.Print strSQL in your code but don't provide the results! What's up with that?

Are both RunDate and TPID actually text/strings? "Me...." can't be part of the SQL. Move it outside the quotes.

strSQL = "UPDATE ERM_FallOut " & vbNewLine _
    & " Set Assigned = '" & Environ("USERNAME") & "' " & vbNewLine _
    & " WHERE ERM_FallOut.SubId IN(" & strCriteria & ") AND [RunDate]='" &  Me.RunDate & "' AND [TPID] = '" & Me.TPID & "'"

Duane
Hook'D on Access
MS Access MVP

 

[ba4crm]

Sorry - guess that would have been helpful!

Thanks for the help - worked like a charm!