Select Certain rsTbl.Field(x)

C

Colby

New member
Local time
Today, 00:18
Joined
Dec 13, 2005
Messages
105
All, I have a query that runs thru and add agents to several dif tables; at least that is what it should do. Right now I am stumbling over how to add a value to a certain field of the rsTbl.
Desc:
the user selects a month from a combo on the form and enters some other info needed and clicks a cmdbutton
this then does the following...

Function AgentADD()

'first we will add to the Agent Table

Set rsTbl = CurrentDb.OpenRecordset("tbl-Agents")
ANtemp = "'" & Trim(Forms("frm-AgentADD")![txtAgentName].Value) & "'"
AIDtemp = "'" & Trim(Forms("frm-AgentADD")![txtAgentID].Value) & "'"

If ANtemp = "''" Or AIDtemp = "''" Or Len(AIDtemp) <> 7 Or Forms("frm-AgentADD")![Text27].Value = "Amount" Then 'if no name or id or if the id is not 5 true characters
MsgBox "You must enter an Agents Name and his/her ID " & vbCrLf & _
"and an Idividual Goal amount. Please try again " & vbCrLf & _
"and include all required information.", vbOKOnly, "Need More Info."

Else 'user typed in a name and id that matches needed criteria

rsTbl.AddNew

rsTbl![Location] = Forms("frm-AgentADD")![txtLocation].Value
rsTbl![AgentID] = Forms("frm-AgentADD")![txtAgentID].Value
rsTbl![AgentName] = Forms("frm-AgentADD")![txtAgentName].Value
rsTbl![SupervisorID] = Forms("frm-AgentADD")![txtSupervisorID].Value
rsTbl![SupervisorName] = Forms("frm-AgentADD")![txtSupervisor].Value
rsTbl![Dept/Bucket] = Forms("frm-AgentADD")![txtDept].Value
If Forms("frm-AgentADD")![cmbTraining].Value = "Yes" Then
rsTbl![C&RTraining] = "Y"
Else
rsTbl![C&RTraining] = "N"
End If

If Forms("frm-AgentADD")![cmbProbation].Value = "Yes" Then
rsTbl![Probation] = "Y"
Else
rsTbl![Probation] = "N"
End If

If Forms("frm-AgentADD")![Combo36].Value = "Yes" Then
rsTbl![Team/Individual] = "T" 'signals a team goal
Else
rsTbl![Team/Individual] = "I" 'signals only an idividual goal. not done prior to self help; all were labeled as T
End If

If Forms("frm-AgentADD")![Combo44].Value = "Yes" Then
rsTbl![LoanLoss] = "Y"
Else
rsTbl![LoanLoss] = "N"
End If

If Forms("frm-AgentADD")![Combo45].Value = "Yes" Then
rsTbl![TeamMultiplier] = "Y"
Else
rsTbl![TeamMultiplier] = "N"
End If

Dim iSend As Integer

iSend = MsgBox("You are about to add " & Forms("frm-AgentADD")![txtAgentName] & vbCrLf & _
"to the active agent list? Is this correct", vbYesNo, "Update Yes/No")
If iSend = 6 Then 'user selected Yes
On Error GoTo Err_Update
rsTbl.update 'this finishes the Agent Table update

'need to update the individual dlq goal table which everyone has
Call Update_IndDlq_Goal

MsgBox "You have successfully added " & Forms("frm-AgentADD")![txtAgentName] & vbCrLf & _
"to the list of active agents."
DoCmd.Close acForm, "frm-AgentADD", acSaveNo
DoCmd.OpenForm "frm-AgentADD"
Else
MsgBox "No records were updated", vbOKOnly, "No Update"
Exit Function
Err_Update:
MsgBox "Please review your entries; something is invalid. " & vbCrLf & _
"This is normally due to a duplicated Agent ID.", vbOKOnly, "Unable to Update"
End If

End If


End Function

Function Update_IndDlq_Goal()

Dim varRST As String

Set rsTbl = Nothing

If Forms("frm-AgentADD")![cmbDate] = "January" Then
varRST = "rsTbl.Fields(3)"
ElseIf Forms("frm-AgentADD")![cmbDate] = "February" Then
varRST = "rsTbl![02-yyyy]"
ElseIf Forms("frm-AgentADD")![cmbDate] = "March" Then
varRST = rsTbl.Fields(5)
End If

Set rsTbl = CurrentDb.OpenRecordset("DlqGoal(Ind)")

rsTbl.AddNew
rsTbl![Dept/Bucket] = Forms("frm-AgentADD")![txtDept].Value
rsTbl![AgentName] = Forms("frm-AgentADD")![txtAgentName].Value
rsTbl![AgentID] = Forms("frm-AgentADD")![txtAgentID].Value
varRST = Forms("frm-AgentADD")![Text27].Value

rsTbl.update

Set rsTbl = CurrentDb.OpenRecordset("tbl-Agents")


End Function


all code works except for what is colored RED. What I am wanting to do is, depending on the month selected, I want to add a value to that specific field.
The DlqGoal(Ind) table adds the name, dept, and id but no value for field(x)!
My table is set up as
F1= dept/bucket
F2= name
F3=id
F4=01-yyyy
F5=02-yyyy
F6=03-yyyy

so if the user selcts "February" then I want the value to populate in rsTbl![02-yyyy].

Any guesses, suggestions, answers maybe

Thanks
 
Im still parsing that post a bit here but just something i noticed:

Code: 
If Forms("frm-AgentADD")![cmbDate] = "January" Then
varRST = "rsTbl.Fields(3)"
ElseIf Forms("frm-AgentADD")![cmbDate] = "February" Then
varRST = "rsTbl![02-yyyy]"
ElseIf Forms("frm-AgentADD")![cmbDate] = "March" Then
varRST = rsTbl.Fields(5)
End If

Notice under "March" the data is populated as rsTbl.Fields(5) where under the other headings the value is populated as a string "rsTbl.Fields(3)" or "rsTbl![02-yyyy]"

Is that a typo?

** Heads back to reading it all **
 
No typo, just where I was testing dif methods. Thanks for taking a look!
 
Ok, I see what you're doing.

Try this:

Code: 
Function Update_IndDlq_Goal()

Dim varRST As String
Dim varSelect As Integer

Set rsTbl = Nothing

If Forms("frm-AgentADD")![cmbDate] = "January" Then
varSelect = 0
ElseIf Forms("frm-AgentADD")![cmbDate] = "February" Then
varSelect = 1
ElseIf Forms("frm-AgentADD")![cmbDate] = "March" Then
varSelect = 2
End If

Set rsTbl = CurrentDb.OpenRecordset("DlqGoal(Ind)")

rsTbl.AddNew
rsTbl![Dept/Bucket] = Forms("frm-AgentADD")![txtDept].Value
rsTbl![AgentName] = Forms("frm-AgentADD")![txtAgentName].Value
rsTbl![AgentID] = Forms("frm-AgentADD")![txtAgentID].Value

Select Case varSelect
Case 0
  rsTbl![01-yyyy] = Forms("frm-AgentADD")![Text27].Value
Case 1
  rsTbl![02-yyyy] = Forms("frm-AgentADD")![Text27].Value
Case 2
  rsTbl![03-yyyy] = Forms("frm-AgentADD")![Text27].Value
End Select

rsTbl.update

Set rsTbl = CurrentDb.OpenRecordset("tbl-Agents")


End Function

Alternatively, MSAccess has a wonderous function that allows dynamic programming.

Code: 
Function Update_IndDlq_Goal()

Dim varRST As String

Set rsTbl = Nothing

If Forms("frm-AgentADD")![cmbDate] = "January" Then
varRST = "rsTbl![01-yyyy]"
ElseIf Forms("frm-AgentADD")![cmbDate] = "February" Then
varRST = "rsTbl![02-yyyy]"
ElseIf Forms("frm-AgentADD")![cmbDate] = "March" Then
varRST = "rsTbl![03-yyyy]"
End If

Set rsTbl = CurrentDb.OpenRecordset("DlqGoal(Ind)")

rsTbl.AddNew
rsTbl![Dept/Bucket] = Forms("frm-AgentADD")![txtDept].Value
rsTbl![AgentName] = Forms("frm-AgentADD")![txtAgentName].Value
rsTbl![AgentID] = Forms("frm-AgentADD")![txtAgentID].Value
[i][COLOR="SeaGreen"]Eval(varRST & " = Forms(""frm-AgentADD"")![Text27].Value")[/COLOR][/i]

rsTbl.update

Set rsTbl = CurrentDb.OpenRecordset("tbl-Agents")


End Function

The Eval Function is defined as such:
function Eval(stringExpr as String)
- stringExpr is script code to be executed.​
 
Last edited:
ReAn, the below is ugly but it works.

rsTbl.AddNew
rsTbl![Dept/Bucket] = Forms("frm-AgentADD")![txtDept].Value
rsTbl![AgentName] = Forms("frm-AgentADD")![txtAgentName].Value
rsTbl![AgentID] = Forms("frm-AgentADD")![txtAgentID].Value
'varRST = Forms("frm-AgentADD")![Text27].Value
If Forms("frm-AgentADD")![cmbDate] = "January" Then
rsTbl![01-yyyy] = Forms("frm-AgentADD")![Text27].Value
ElseIf Forms("frm-AgentADD")![cmbDate] = "February" Then
rsTbl![02-yyyy] = Forms("frm-AgentADD")![Text27].Value
ElseIf Forms("frm-AgentADD")![cmbDate] = "March" Then
rsTbl![03-yyyy] = Forms("frm-AgentADD")![Text27].Value
End If

rsTbl.update
 
Tried the eval but debug did not like it; I am using the ugly but will keep your piece in my hat for next version.
 
Colby said:
Tried the eval but debug did not like it; I am using the ugly but will keep your piece in my hat for next version.
Click to expand...

Can you post the output from debug?

Also, you may want to replace

"rsTbl![01-yyyy]" with "rsTbl('01-yyyy')" that might help.
 
Last edited:
Ah yes, I looked into the Eval() Function, the problem arises because the function is part of the Application Object, in turn it's Scope is outside you're function, thus rsTbl dosent exist as far as Eval is concerned.
 
Not tested but just saving the index should work.

If Forms("frm-AgentADD")![cmbDate] = "January" Then
varRST = 3
ElseIf Forms("frm-AgentADD")![cmbDate] = "February" Then
varRST = 4
ElseIf Forms("frm-AgentADD")![cmbDate] = "March" Then
varRST = 5
End If

Set rsTbl = CurrentDb.OpenRecordset("DlqGoal(Ind)")

rsTbl.AddNew
rsTbl![Dept/Bucket] = Forms("frm-AgentADD")![txtDept].Value
rsTbl![AgentName] = Forms("frm-AgentADD")![txtAgentName].Value
rsTbl![AgentID] = Forms("frm-AgentADD")![txtAgentID].Value
rsTbl(varRST) = Forms("frm-AgentADD")![Text27].Value

Edit: varRST will need to be dimmed as integer of course!

HTH

Peter
 
You must log in or register to reply here.

Similar threads

C
Solved Passing values from form to report
Replies
5
Views
812
ClaraBarton
C
oxicottin
Solved Multiselect listbox loop showing there isn't data
Replies
4
Views
701
pbaldy
pbaldy
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
1,384
The_Doc_Man
The_Doc_Man
R
Solved Dreaded 'Write Conflict' error after adding a new field to Access form
Replies
12
Views
706
ricardocpa
R
brucemc777
How to populate a combobox with multiselect filepicker results, then save results?
Replies
3
Views
426
brucemc777
brucemc777

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom