Field value based on another field

[Guest]

Hello,
I'm sorry to be asking such a stupid question but I have a table with a
numeric value in one field and I want a second field to be based on the value
of the numeric field, how would I do that? Thank you so much for your help.

 

[gumby]

Where is the second field located?

 

[Guest]

I'm sorry, I wrote "table" when I should have written "form". Both fields
are on the same form. Thanks.

 

[Steve Schapel]

Joanne,

Well, to be accurate here, forms do not have fields. Fields are in
tables (and queries), and forms can have controls that serve as a
convenient way of viewing the data in the fields. Normally it would be
somewhat pedantic of me to point this out, but in this case I think it
is relevant to your question. If a value can be calculated from, or
derived from, existing data, then it is very unlikely that you want a
second field based on the value of the first. This would almost
certainly be an invalid database design. On the other hand, it is often
a legitimate requirement to have a control on a form to *display* (as
distinct from store) a derived or looked-up value. This is normally
done via either a calculated field in the query that the form is based
on, or via an expression in the Control Source property of an unbound
textbox on the form. So, sorry can't be more explicit without more
details of what you want... if you can post back with a specific
example, then someone may be able to advise more precisely.

 

[gumby]

Have the second field's default value set to =[Field1Name]

 

[gumby]

Have the second text ield's default value set to =[Field1Name]

 

[gumby]

Have the second text field's default value set to =[Field1Name]

 

[Steve Schapel]

Sorry, Gumby, this is not useful. The Default Value only has an effect
at the point where a new record is first started, at which point
Field1Name has no value.

 

[Guest]

Thank you both very much for your help. To be more explicit, I have a drop
down list which allows the user to make multiple choices from the list. As
the user picks the names from the list, I would like the result to appear in
an adjacent text box. This is the code that I now have:

Me.txtAuthorofMajority.ControlSource =
Forms!frmAllData!JusticeAuthorOfMajority.Column(1)

I just get a #Name? error when I try to run this. And by the way, thank you
for pointing out that forms don't have fields, but controls. I'm very new to
this and it was quite helpful conceptually.

Sorry, Gumby, this is not useful. The Default Value only has an effect
at the point where a new record is first started, at which point
Field1Name has no value.

--
Steve Schapel, Microsoft Access MVP

Have the second text field's default value set to =[Field1Name]

 

[Guest]

I actually figured out the problem, which has now presented a smaller
problem. This is the code I am using:

strSql = "DELETE FROM tblChosenJustices WHERE CaseName=" &
Form_frmAllData.CaseName
db.Execute strSql, dbFailOnError

With Me.JusticeAuthorOfMajority

For Each varItem In .ItemsSelected
strSql = _
"INSERT INTO tblChosenJustices" & "" _
& "(CaseName,JudgeID)" & _
"VALUES (" & _
Form_frmAllData.CaseName & "," & .ItemData(varItem) & ")"
db.Execute strSql, dbFailOnError

strList = strList & .Column(1, varItem) & Chr(13)
Next varItem
End With
____
The weird thing is that Chr(13) just gives me the standard box display and
not an actual carriage return between entries. Is there a different Chr
number I should be using? I also tried 10. That doesn't seem to work either.
Thanks for your help.

 

[Steve Schapel]

Joanne,

You need...
Chr(13) & Chr(10)
or...
vbCrLf

 

[Steve Schapel]

Joanne,

I am not 100% clear what you are trying to achieve here, but...
Forms!frmAllData!JusticeAuthorOfMajority.Column(1)
.... cant be the Control Source of a form control.
Can you give some more info, maybe an example?

 

[Guest]

Thank you very much. That worked perfectly. If I could prevail upon you to
answer one more question - is it possible to store the values of what is
displayed in the textbox (strList), even if the user exits the form? Then
the values would only change if the user updated the original list box. I'm
guessing I might have to write to a reg. key. Is that correct?

 

[Steve Schapel]

Joanne,

Aren't you writing the values to the tblChosenJustices table? Won't
they still be available from there the next time you open the database?

 

[Guest]

Yes I do, but the "OnCurrent" event does not seem to be firing. I put a
message box in here and it never displays. It does not get to the
ClearChoices subroutine either. Can you tell me what's wrong with it?
Thanks very much.

Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim intI As Integer
Dim strListOpen As String
ClearChoices
If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset( _
"SELECT JudgeId from tblChosenJustices WHERE CaseName=" &
Form_frmAllData.CaseName)

With Me.JusticeAuthorOfMajority
Do Until rs.EOF
For intI = 0 To (.ListCount - 1)
If .ItemData(intI) = CStr(rs!JudgeId) Then
.Selected(intI) = True
strListOpen = strListOpen & .ItemData(intI) & vbCrLf
Exit For
End If

Next intI
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End With
End If

Me.txtAuthorofMajority.ControlSource = strListOpen
End Sub

 

[Steve Schapel]

Joanne,

What is ClearChoices?

What is Form_frmAllData? Is it open at the time?

Is CaseName a number or text data type?

I assume JusticeAuthorOfMajority is a Listbox. Right? What is the
bound column? Does it correspond with the JudgeID field? Is it a text
or number data type? What is the purpose of the CStr() function in
".ItemData(intI) = CStr(rs!JudgeId)"

What is this?...
Me.txtAuthorofMajority.ControlSource = strListOpen
Can you give an example of what you want to set the Control Source of
this textbox to? I can't see that strListOpen will be correct.

Can you simply descibe the purpose of this code, I'm afraid I can't
understand what you are tyring to do?