Jeanette
Thanks for your help but I'm still having problems on both counts so I've
uploaded some screen shots (which may help to explain) to here :-
www.blue-bean.co.uk/access010109.zip (its only 65kb)
Your solution makes perfect sense to me however I'll explain how I've
gone
about the problem so far.
Have a look at 1.jpg as this shows the controls as they are on my form.
The
tickbox is called chkAddWorks, the cbo is called cboPreviousJob, the
first
txt box is called txtAddWorksClient and the second tick box is called
txtAddWorksSite. The button on the right is called cmdOpenJobForm.
I have code on the Form_Load event which enables / disables the above
controls depending on whether the chkAddWorks is ticked or not (see
1a.jpg).
The code is as follows:-
Private Sub Form_Load()
If Me.chkAddWorks = -1 Then
'set conditional states
Me.cboPreviousJob.Enabled = True
Me.txtAddWorksClient.Enabled = True
Me.txtAddWorksSite.Enabled = True
Me.cmdOpenJobForm.Enabled = True
Else
'conditions if checkbox is not ticked
Me.cboPreviousJob.Enabled = False
Me.txtAddWorksClient.Enabled = False
Me.txtAddWorksSite.Enabled = False
Me.cmdOpenJobForm.Enabled = False
End If
End Sub
If I ticked the chkAddWorks then I had some code on the cmdAddWorks_Click
event as follows:-
Private Sub chkAddWorks_Click()
If Me.chkAddWorks = -1 Then
Me.cboPreviousJob.Enabled = True
Me.txtAddWorksClient.Enabled = True
Me.txtAddWorksSite.Enabled = True
Me.cmdOpenJobForm.Enabled = True
Else
Me.cboPreviousJob.Enabled = False
Me.txtAddWorksClient.Enabled = False
Me.txtAddWorksSite.Enabled = False
Me.cmdOpenJobForm.Enabled = False
End If
Me![cboPreviousJob] = Null
Me![txtAddWorksClient] = Null
Me![txtAddWorksSite] = Null
End Sub
This all works fine (but doesn't if I implement your solution. I get
Run-Time error 2448 "You cant assign a value to this object" and the
debug
breaks the code as in 3.jpg)
Before I read your solution I had code on the after_update event of the
cboPreviousJob as follows:-
Private Sub cboPreviousJob_AfterUpdate()
Me.txtAddWorksClient = Me.cboPreviousJob.Column(1)
Me.txtAddWorksSite = Me.cboPreviousJob.Column(2)
End Sub
This seemed to work, but if I cycled to another record then the
cboPreviousJob would change as it should but the two txt controls
maintained
the values from the previous record, so I knew something wasn't working,
hence the original post.
I now get the #Name appearing in my txt boxes using your solution.
Also with your code for the cbo box (as below) this also won't work. The
code needs to be on the click event of the cmd button, not the cbo box.
Private Sub cboOldJob_AftetUpdate()
If Len(Me.cboOldJob & vbNullString) >0 Then
DoCmd.OpenForm "[NameOfForm]", , , "[JobID] = " & Me.cboOldJob & ""
DoCmd.Close acForm, Me.Name
End If
I want to tick the chkbox select the job (which then populates the 2 txt
boxes) and then clicking the button opens that Job using frmJobs. The
previous code I used on this button was:-
Private Sub cmdOpenJobForm_Click()
On Error GoTo Err_cmdOpenJobForm_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmJobs"
stLinkCriteria = "[cboPreviousJob]=" & Forms![frmJobs]![JobNo]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenJobForm_Click:
Exit Sub
Err_cmdOpenJobForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenJobForm_Click
End Sub
This gave me an error saying it cant find frmJobs?????? 4.jpg shows the
form exists?
I modified your code from above in an attempt to solve this as follows:-
Private Sub cmdOpenJobForm_Click()
If Len(Me.cboPreviousJob & vbNullString) > 0 Then
DoCmd.OpenForm "[frmJobs]", , , "[JobNo] = " & Me.cboPreviousJob & ""
DoCmd.Close acForm, Me.Name
End If
End Sub
But this gives me a similar issue as my previous code (se 5.jpg)
I appreciate that your advice is correct but am I right in thinking
everything above is linked to the problem. I know you can acheieve the
same
thing several ways in access and maybe the way I've implemented certain
bits
is what is causing me the problem
Thanks for your time. Happy New Year
--
Regards
Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Jeanette Cunningham said:
Hi Andy,
You can use the after update event for cboOldJob to open a form filtered
to that job.
Private Sub cboOldJob_AftetUpdate()
If Len(Me.cboOldJob & vbNullString) >0 Then
DoCmd.OpenForm "[NameOfForm]", , , "[JobID] = " & Me.cboOldJob & ""
DoCmd.Close acForm, Me.Name
End If
If you set up one of the unbound text boxes with its control source =
Me.cboOldJob.Column(1)
It will show the value for the client name for the job selected in the
combo.
When the combo is empty (Null), the textbox will also be empty.
When you change the value in the combo, the textbox will change and show
the matching client name.
Assuming that the second column of the combo has the info about client
name.
If it is a different column, change the number for the column in the
textbox's control source expression.
Combo's columns are numbered starting at 0 for the 1st column.
Do a similar thing with the other textbox.
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
2 Problems.
1. I have a form which displays all the information for each project I
undertake (one project one form). On that form I have a bound combo
box
(cboOldJob) which queries all the other projects i have undertaken and
allows me to select a previous project which is linked to the current
one. This works ok.
I have two unbound txt boxes which display the client name and the job
location when I select the job from the bound cbo (cboOldJob). The
problem is that these unbound txt boxes don't refresh / requery when
the
form is loaded even though the cbo still has the old job value stored.
I
have a requery on the formload event as follows (which doesn't seem to
work):-
txtOldJobClient.Requery
txtOldJobSite.Requery
2. I have a button next to the cboOldJob which I want to open a form
and
filter the result to the value in cboOldJob. How do I do this?
--
Regards
Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
