Tina,
My head is hurting now! I still cannot get this to work!
If you don't mind, I'll describe--in it's entirety--the form, table(s) and
controls that I'm using, and see if you can help me with it...please?
Table 1 - tblProjects
fldProjectID = Number
fldProjectDescription = Text
fldValidUntilDate = Date/Time
fldIsWork = Yes/No
Table 2 - tblTimesheet
This is the main table that stores all information relating to projects
worked on by employees. The field I am concerned with is:
fldProjectID = Number
This field is linked to the table tblProjects, linking to the fldProjectID
field.
As per your instructions, my main form has two controls on it for the Project
Description:
Control 1 - cboProjectID
ColumnCount = 3
ColumnWidths = 11.401cm;1.801cm;2.501cm
Visible = Yes
ControlSource = tblTimeSheet.fldProjectID
RowSource* = SELECT tblProjects.fldProjectDescription, _
tblProjects.fldProjectID, _
tblProjects.fldValidUntilDate FROM _
tblProjects WHERE _
(((tblProjects.fldValidUntilDate)>Date())) _
ORDER BY tblProjects.fldProjectID;
* I altered the RowSource here to reflect the way the information should
be displayed and sorted.
BoundColumn* = 2
* Once again, I had to alter the value above. With the value you
suggested--1--all I was getting displayed in the field was the Project
code and not the text. This was also making it difficult to select
any values.
LimitToList = Yes
Enabled = Yes
Locked = No
TabStop = No
When I use the code you suggested for the cboProjectID events, the
following happens:
In the GotFocus event, Me!cboProjectID.Column(2) is the ValidUntilDate
field, if I am correct. This being the case, the date is always populated
and so the Locked property of the control cboProjectID will never be set
to "True" here. Am I right?
Control 2 - txtProjectDescription
ControlSource* =
=IIf([fldProjectID] Is Null,Null,DLookUp("[fldProjectDescription]", _
"tblProjects","[fldProjectID] = " & _
Forms!frmTimesheet!fldProjectID))
* So that I understand what is going on here, am I right in thinking:
If the fldProjectID if Null--this would be the one from the main
table--tblTimeSheet--then set the control to Null. Otherwise, lookup
the value in the field fldProjectDescription, within the table
tblProjects,
where the value held in fldProjectID is the same as the value in the
control fldProjectID on the form frmTimesheet.
If my thinking is correct, then the lookup above is wrong. There is no
control on the form named 'fldProjectID'. Did you mean this to be
[tblTimesheet]![fldProjectID]?
Another problem I have noted with this control is when I create a new
record. Tabbing into text field, displays an initial value of '#Name?'
when I try to add a new record. From the ControlSource above, I
would expect that a new record would have a fldProjectID of Null, so
it should display a Null value in the text control. I've tried various
combinations of controls and field values for this, and I just can't get
it to work.
Sorry for the long post!! I just need to get my head around a few things, some
of which I have probably messed up when translating from your code to mine.
The only other thing I miss is the ability to enter data and the combo to match
it with the first entry in its list. But still, I always did want to have my
cake AND eat it!!
Many, many thanks for your help with this Tina, and I patiently await you reply.
If you think your test db will explain things better, please feel free to e-mail
me it. Just remove the underscores and everything in between.
Rgds
Duncan
--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.
Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.
tina said:
i couldn't get it to work either, so we're both missing "something".
however, i created a test db, using the table and field names you provided
(you have no idea how much that helps!). i set up a "text box on top of
combo box" solution similar to George's, except that it shows the
fldProjectDescription at all times, on both Single Form view and Continuous
Form view. *note: the solution does not work as intended on Datasheet view,
because the text box and combo box will show up as side by side columns
regardless of how they're positioned in Design view.*
here's my tested solution, in case you want to give it a try. (also, if you
want to *see* it, i can email my test db to you.)
1. keep both the text box and the combo box on the form.
2. delete the OnEnter and AfterUpdate code.
3. set the properties of the text box as follows:
Name: txtProjectDescription
ControlSource: =IIf([fldProjectID] Is
Null,Null,DLookUp("[fldProjectDescription]","tblProjects","[fldProjectID] =
" & [Forms]![frmTimesheet]![fldProjectID]))
*note: enter the above, including the = sign, all on a single line in the
ControlSource property. also, check the table, field, form and control names
to make sure they match the names in your database. if they don't, fix
them.*
Enabled: Yes
Locked: Yes
Tabstop: Yes
4. set the properties of the combo box as follows:
Name: cboProjectID
ControlSource: fldProjectID
RowSource: SELECT tblProjects.fldProjectID,
tblProjects.fldProjectDescription, tblProjects.fldValidUntilDate FROM
tblProjects WHERE (((tblProjects.fldValidUntilDate)>Date())) ORDER BY
tblProjects.fldProjectDescription;
*note: enter the above SQL statement all on a single line in the RowSource
property.*
ColumnCount: 3
ColumnWidths: 0";1";0"
*note: widths are in inches - sorry, i don't know the metric conversions,
so you'll have to convert one inch to the appropriate width for your
Description column.*
BoundColumn: 1
Limit to List: Yes
Enabled: Yes
Locked: No
Tabstop: No
5. on the menu bar, click View, TabOrder. set txtProjectDescription *above*
cboProjectID.
6. move the text box directly on top of the combo box *but* drag the right
edge of the text box to the left until you can see the entire "down arrow"
at the right side of the combo box.
7. paste the following code into the form's module. *check all the control
and form names to make sure they match the names in your database - if not,
fix them.*
Private Sub cboProjectID_GotFocus()
If Not Me.NewRecord Then
With Me!cboProjectID
If IsNull(.Column(2)) Then
.Locked = True
End If
End With
End If
End Sub
Private Sub cboProjectID_LostFocus()
Me!cboProjectID.Locked = False
End Sub
Private Sub txtProjectDescription_GotFocus()
With Me!cboProjectID
If .Column(2) > Date Or IsNull(.Value) Then
.SetFocus
End If
End With
End Sub
8. save the form, then close it.
re-open the form, and test it out. my db solution ran without error on a
mixture of "old" project records, "new" project records, and just flat-out
new records. it's not a perfect solution, but as close as i could get.
hth
