Control source error

  • Thread starter Thread starter Rasho
  • Start date Start date
R

Rasho

I have created a database program that provides information for crews and
plants. Part of this program requires a whiteboard similar to a Gantt chart
showing the contractor and location in a column on the left of the page,
dates and day of the week in a row along the top of the page, and the
designated crew corresponding to the contractor and the date.
The data collected is through a cross tab query with common links, the final
link that the cross tab is formed on is a select query that only provides
dates within a set period, and only if the crews are working in that period
i.e days allocated = 10; days working = 9. This means that one date is
missing in the cross tab query.
The date column auto changes from the date the program is opened, and the
crew allocation is based on the date using visual basic code
(Me.Crew_1.ControlSource = Me.Date_1). This works fine when the cross tab
query has a column with the specified date, however when no date exists a
database error appears in the control (#Name?).
Is there a code I can use to skip this database era and resume next so that
the control source era is ignored?
 
Hi Rasho,

Not sure if this will help but try using the Nz function so that it is not
trying to assign a null value.

Me.Crew_1.ControlSource = Nz(Me.Date_1,0)
 
Hi Ossie Mac,
Tried your suggestion. The Nz function still didn't solve the problem.
Have noted that this is "Trappable Microsoft Jet and DAO Error (2424)". Do
you know of any code or funtion that can bypass or nutralise the (Unknown
Name error).

Regards,

Rasho
 
Hi Rasho,

Unknown name tends to indicate that something does not exist. When using Me
with the controls are you in the forms code area? If not then you need the
full form name.

Also are the relevant form/s open?
 
Hi OssieMac,
The code is for an open form that seeks a control source from a crosstab
query that only specifies the date (column heading) when a crew works. The
visual basic code used to seek this control source
Me.Crew_1.ControlSource = Me.Date_1 (Date)
Me.Crew_2.ControlSource = Me.Date_2 (Date + 1)
and so on for 30 days. In this period they might not work weekends as a
result the control has no control source thus providing the 'Unknown name'
value. I am trying to provide in the controls either the value belonging to
the control source or alternatively if there is no control source, provide a
null value so that the #Name? value does not appear.

Regards,

Rasho
 
Hi Rasho,

The problem that I encountered is that with the code you can't get past the
error, even using On Error Resume Next, if the object does not exist because
the code will not compile if it refers to a nonexistant object.

I have not tested using Pivot tables but I did the following test just using
text boxes so that the actual object is not used in the code if it does not
exist. Perhaps you can adapt it to your use. I tested with the text box
existing as well as not existing and it worked well.

I was not able to set the control source without concatenating the equals
sign. (Square brackets can be omitted if no spaces in the controlsource name.)

Private Sub AssignCtrlSource_Click()

Dim objCtrl As Object
Dim varSourceName As String

For Each objCtrl In Controls
If objCtrl.Name = "ValidSource" Then
varSourceName = objCtrl.Name
Exit For
End If
Next

If Nz(varSourceName, "") <> "" Then 'ControlSource object exists
'Concatenate the equals sign and square brackets
Me.TestCtrlSource.ControlSource = "= [" & varSourceName & "]"
Else
Me.TestCtrlSource.ControlSource = ""
End If

End Sub
 
Hi OssieMac,
I tried implementing the code you supplied in the program it still didn’t
work, so I tried replicating what you did with a twist to simulate what I
need. The following is the stage I am at now I hope this explains my dilemma
more accurately to you or any one else:
• I created a table with Field names Data1, Data2 and ID.
• I added data to the first row of the table.
• I created a form with the same text box names as the table. Eventually I
changed Data1 to TestCtrlSource and Data2 to ValidSource. Also on this form I
created a command button and named it AssignCtrlSource.
• I then modified the code you supplied after repeated attempts to get the
required result.

Private Sub AssignCtrlSource_Click()
Dim objCtrl As Object
Dim varSourceName As String

For Each objCtrl In Controls
If objCtrl.Name = "ValidSource" Then
varSourceName = objCtrl.Name
Exit For
End If
Next

If Nz(varSourceName, "") <> "" Then 'ControlSource object exists
'Concatenate the equals sign and square brackets
Me.TestCtrlSource.ControlSource = "= [" & ValidSource & "]"
Else
Me.TestCtrlSource.ControlSource = ""
End If

End Sub

• By changing the value in the ValidSource (Data2) control I get the
required result in TestCtrlSource (Data1) if I type in the value Data1 or
Data2. If I type in the value Data3 the no object message appears (#Name?) in
TestCtrlSource (Data1) instead of getting the expected blank text box.

Summary
It is the value that ValidSource (Data2) has input that controls the control
source. If this value does not resemble any field names I want the respective
text box to return a null value. In the scenario above TestCtrlSource (Data1)
should return a null value if no field exist in the table.

Are the changes I made heading in the right direction, and what can I do to
achieve the required results bearing in mind that when used with the intended
program it will be for multiple text boxes and fields.

Regards,

Rasho


OssieMac said:
Hi Rasho,

The problem that I encountered is that with the code you can't get past the
error, even using On Error Resume Next, if the object does not exist because
the code will not compile if it refers to a nonexistant object.

I have not tested using Pivot tables but I did the following test just using
text boxes so that the actual object is not used in the code if it does not
exist. Perhaps you can adapt it to your use. I tested with the text box
existing as well as not existing and it worked well.

I was not able to set the control source without concatenating the equals
sign. (Square brackets can be omitted if no spaces in the controlsource name.)

Private Sub AssignCtrlSource_Click()

Dim objCtrl As Object
Dim varSourceName As String

For Each objCtrl In Controls
If objCtrl.Name = "ValidSource" Then
varSourceName = objCtrl.Name
Exit For
End If
Next

If Nz(varSourceName, "") <> "" Then 'ControlSource object exists
'Concatenate the equals sign and square brackets
Me.TestCtrlSource.ControlSource = "= [" & varSourceName & "]"
Else
Me.TestCtrlSource.ControlSource = ""
End If

End Sub
Click to expand...
 
Hi Rasho,

In your example, you have now added a table field that becomes the initial
control source. Therefore you would need to test if the table fields exists
and it would be something like this. However, I don't think this is going to
solve your initial problem; it only gives sample code of how you might solve
it. (More explanation on this below)

Sub test()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strFieldName As String


Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("Table1")

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "Data1" Then
strFieldName = fldCurr.Name
Exit For
End If
Next
If fldCurr.Name <> "" Then
MsgBox "Field Data1 exists"
Else
MsgBox "Field Data1 does NOT exist"
End If

End Sub

Now if I understand correctly, your original post indicated that the control
source was bound to a Pivot Table field that might or might not exist in
which case you need to loop through the Pivot table fields. However, I don't
know how (or if it is possible) to loop through the pivot table fields in a
similar manner to the above. Therefore I suggest that you put up a new post
and ask the question:

"Is it possible to loop through a Pivot tables fields using a For Each type
loop or
For i = 1 to Pivot table fields.count type loop?"

If you can get code to do the above then your problem is all but solved but
if not then I am afraid that I don't know the answer.


--
Regards,

OssieMac


Rasho said:
Hi OssieMac,
I tried implementing the code you supplied in the program it still didn’t
work, so I tried replicating what you did with a twist to simulate what I
need. The following is the stage I am at now I hope this explains my dilemma
more accurately to you or any one else:
• I created a table with Field names Data1, Data2 and ID.
• I added data to the first row of the table.
• I created a form with the same text box names as the table. Eventually I
changed Data1 to TestCtrlSource and Data2 to ValidSource. Also on this form I
created a command button and named it AssignCtrlSource.
• I then modified the code you supplied after repeated attempts to get the
required result.

Private Sub AssignCtrlSource_Click()
Dim objCtrl As Object
Dim varSourceName As String

For Each objCtrl In Controls
If objCtrl.Name = "ValidSource" Then
varSourceName = objCtrl.Name
Exit For
End If
Next

If Nz(varSourceName, "") <> "" Then 'ControlSource object exists
'Concatenate the equals sign and square brackets
Me.TestCtrlSource.ControlSource = "= [" & ValidSource & "]"
Else
Me.TestCtrlSource.ControlSource = ""
End If

End Sub

• By changing the value in the ValidSource (Data2) control I get the
required result in TestCtrlSource (Data1) if I type in the value Data1 or
Data2. If I type in the value Data3 the no object message appears (#Name?) in
TestCtrlSource (Data1) instead of getting the expected blank text box.

Summary
It is the value that ValidSource (Data2) has input that controls the control
source. If this value does not resemble any field names I want the respective
text box to return a null value. In the scenario above TestCtrlSource (Data1)
should return a null value if no field exist in the table.

Are the changes I made heading in the right direction, and what can I do to
achieve the required results bearing in mind that when used with the intended
program it will be for multiple text boxes and fields.

Regards,

Rasho


OssieMac said:
Hi Rasho,

The problem that I encountered is that with the code you can't get past the
error, even using On Error Resume Next, if the object does not exist because
the code will not compile if it refers to a nonexistant object.

I have not tested using Pivot tables but I did the following test just using
text boxes so that the actual object is not used in the code if it does not
exist. Perhaps you can adapt it to your use. I tested with the text box
existing as well as not existing and it worked well.

I was not able to set the control source without concatenating the equals
sign. (Square brackets can be omitted if no spaces in the controlsource name.)

Private Sub AssignCtrlSource_Click()

Dim objCtrl As Object
Dim varSourceName As String

For Each objCtrl In Controls
If objCtrl.Name = "ValidSource" Then
varSourceName = objCtrl.Name
Exit For
End If
Next

If Nz(varSourceName, "") <> "" Then 'ControlSource object exists
'Concatenate the equals sign and square brackets
Me.TestCtrlSource.ControlSource = "= [" & varSourceName & "]"
Else
Me.TestCtrlSource.ControlSource = ""
End If

End Sub
Click to expand...
Click to expand...
 
OssieMac
Thanks for the time in trying to help me solve this problem.

Cheers,
Rasho

OssieMac said:
Hi Rasho,

In your example, you have now added a table field that becomes the initial
control source. Therefore you would need to test if the table fields exists
and it would be something like this. However, I don't think this is going to
solve your initial problem; it only gives sample code of how you might solve
it. (More explanation on this below)

Sub test()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strFieldName As String


Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("Table1")

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "Data1" Then
strFieldName = fldCurr.Name
Exit For
End If
Next
If fldCurr.Name <> "" Then
MsgBox "Field Data1 exists"
Else
MsgBox "Field Data1 does NOT exist"
End If

End Sub

Now if I understand correctly, your original post indicated that the control
source was bound to a Pivot Table field that might or might not exist in
which case you need to loop through the Pivot table fields. However, I don't
know how (or if it is possible) to loop through the pivot table fields in a
similar manner to the above. Therefore I suggest that you put up a new post
and ask the question:

"Is it possible to loop through a Pivot tables fields using a For Each type
loop or
For i = 1 to Pivot table fields.count type loop?"

If you can get code to do the above then your problem is all but solved but
if not then I am afraid that I don't know the answer.


--
Regards,

OssieMac


Rasho said:
Hi OssieMac,
I tried implementing the code you supplied in the program it still didn’t
work, so I tried replicating what you did with a twist to simulate what I
need. The following is the stage I am at now I hope this explains my dilemma
more accurately to you or any one else:
• I created a table with Field names Data1, Data2 and ID.
• I added data to the first row of the table.
• I created a form with the same text box names as the table. Eventually I
changed Data1 to TestCtrlSource and Data2 to ValidSource. Also on this form I
created a command button and named it AssignCtrlSource.
• I then modified the code you supplied after repeated attempts to get the
required result.

Private Sub AssignCtrlSource_Click()
Dim objCtrl As Object
Dim varSourceName As String

For Each objCtrl In Controls
If objCtrl.Name = "ValidSource" Then
varSourceName = objCtrl.Name
Exit For
End If
Next

If Nz(varSourceName, "") <> "" Then 'ControlSource object exists
'Concatenate the equals sign and square brackets
Me.TestCtrlSource.ControlSource = "= [" & ValidSource & "]"
Else
Me.TestCtrlSource.ControlSource = ""
End If

End Sub

• By changing the value in the ValidSource (Data2) control I get the
required result in TestCtrlSource (Data1) if I type in the value Data1 or
Data2. If I type in the value Data3 the no object message appears (#Name?) in
TestCtrlSource (Data1) instead of getting the expected blank text box.

Summary
It is the value that ValidSource (Data2) has input that controls the control
source. If this value does not resemble any field names I want the respective
text box to return a null value. In the scenario above TestCtrlSource (Data1)
should return a null value if no field exist in the table.

Are the changes I made heading in the right direction, and what can I do to
achieve the required results bearing in mind that when used with the intended
program it will be for multiple text boxes and fields.

Regards,

Rasho


OssieMac said:
Hi Rasho,

The problem that I encountered is that with the code you can't get past the
error, even using On Error Resume Next, if the object does not exist because
the code will not compile if it refers to a nonexistant object.

I have not tested using Pivot tables but I did the following test just using
text boxes so that the actual object is not used in the code if it does not
exist. Perhaps you can adapt it to your use. I tested with the text box
existing as well as not existing and it worked well.

I was not able to set the control source without concatenating the equals
sign. (Square brackets can be omitted if no spaces in the controlsource name.)

Private Sub AssignCtrlSource_Click()

Dim objCtrl As Object
Dim varSourceName As String

For Each objCtrl In Controls
If objCtrl.Name = "ValidSource" Then
varSourceName = objCtrl.Name
Exit For
End If
Next

If Nz(varSourceName, "") <> "" Then 'ControlSource object exists
'Concatenate the equals sign and square brackets
Me.TestCtrlSource.ControlSource = "= [" & varSourceName & "]"
Else
Me.TestCtrlSource.ControlSource = ""
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi OzzieMac,
I played around with the last code you posted and found that it created a
trappable error that I was able to turn to my favour.

Private Sub AssignCtrlSource_Click()
On Error GoTo errControl

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strFieldName As String

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("Table1")

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "Data1" Then
strFieldName = fldCurr.Name
Exit For
End If
Next
If fldCurr.Name <> "" Then
Me.TestCtrlSource.ControlSource = strFieldName
Else
Me.TestCtrlSource.ControlSource = ""
End If

errControl:
If Err.Number = 91 Then
Me.TestCtrlSource.ControlSource = ""
End If
Resume Next
End Sub

All I had to do to revert this back to the original question was to change
the reference to the crosstab query as follows:
Sub C1()
On Error GoTo errControl
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim fldCurr As DAO.Field
Dim strFieldName As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("qry_BACWhite_register")

For Each fldCurr In qdfCurr.Fields
If fldCurr.Name = Date Then
strFieldName = fldCurr.Name
Exit For
End If
Next
If fldCurr.Name <> "" Then
Me.Crew_1.ControlSource = strFieldName
End If

errControl:
If Err.Number = 91 Then
Me.Crew_1.ControlSource = ""
End If
Resume Next
End Sub

I then called each procedure to the relevant command. This worked well for
both Forms and Reports:

Private Sub Form_Activate()
DoCmd.Maximize
Call C1
Call C2
Call C3
End Su
--------------------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)
Call C1
Call C2
Call C3
End Sub

Thankyou OssieMac for the effort any the valuable information you have
provided.

Regards,

Rasho


OssieMac said:
Hi Rasho,

In your example, you have now added a table field that becomes the initial
control source. Therefore you would need to test if the table fields exists
and it would be something like this. However, I don't think this is going to
solve your initial problem; it only gives sample code of how you might solve
it. (More explanation on this below)

Sub test()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strFieldName As String


Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("Table1")

For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "Data1" Then
strFieldName = fldCurr.Name
Exit For
End If
Next
If fldCurr.Name <> "" Then
MsgBox "Field Data1 exists"
Else
MsgBox "Field Data1 does NOT exist"
End If

End Sub

Now if I understand correctly, your original post indicated that the control
source was bound to a Pivot Table field that might or might not exist in
which case you need to loop through the Pivot table fields. However, I don't
know how (or if it is possible) to loop through the pivot table fields in a
similar manner to the above. Therefore I suggest that you put up a new post
and ask the question:

"Is it possible to loop through a Pivot tables fields using a For Each type
loop or
For i = 1 to Pivot table fields.count type loop?"

If you can get code to do the above then your problem is all but solved but
if not then I am afraid that I don't know the answer.


--
Regards,

OssieMac


Rasho said:
Hi OssieMac,
I tried implementing the code you supplied in the program it still didn’t
work, so I tried replicating what you did with a twist to simulate what I
need. The following is the stage I am at now I hope this explains my dilemma
more accurately to you or any one else:
• I created a table with Field names Data1, Data2 and ID.
• I added data to the first row of the table.
• I created a form with the same text box names as the table. Eventually I
changed Data1 to TestCtrlSource and Data2 to ValidSource. Also on this form I
created a command button and named it AssignCtrlSource.
• I then modified the code you supplied after repeated attempts to get the
required result.

Private Sub AssignCtrlSource_Click()
Dim objCtrl As Object
Dim varSourceName As String

For Each objCtrl In Controls
If objCtrl.Name = "ValidSource" Then
varSourceName = objCtrl.Name
Exit For
End If
Next

If Nz(varSourceName, "") <> "" Then 'ControlSource object exists
'Concatenate the equals sign and square brackets
Me.TestCtrlSource.ControlSource = "= [" & ValidSource & "]"
Else
Me.TestCtrlSource.ControlSource = ""
End If

End Sub

• By changing the value in the ValidSource (Data2) control I get the
required result in TestCtrlSource (Data1) if I type in the value Data1 or
Data2. If I type in the value Data3 the no object message appears (#Name?) in
TestCtrlSource (Data1) instead of getting the expected blank text box.

Summary
It is the value that ValidSource (Data2) has input that controls the control
source. If this value does not resemble any field names I want the respective
text box to return a null value. In the scenario above TestCtrlSource (Data1)
should return a null value if no field exist in the table.

Are the changes I made heading in the right direction, and what can I do to
achieve the required results bearing in mind that when used with the intended
program it will be for multiple text boxes and fields.

Regards,

Rasho


OssieMac said:
Hi Rasho,

The problem that I encountered is that with the code you can't get past the
error, even using On Error Resume Next, if the object does not exist because
the code will not compile if it refers to a nonexistant object.

I have not tested using Pivot tables but I did the following test just using
text boxes so that the actual object is not used in the code if it does not
exist. Perhaps you can adapt it to your use. I tested with the text box
existing as well as not existing and it worked well.

I was not able to set the control source without concatenating the equals
sign. (Square brackets can be omitted if no spaces in the controlsource name.)

Private Sub AssignCtrlSource_Click()

Dim objCtrl As Object
Dim varSourceName As String

For Each objCtrl In Controls
If objCtrl.Name = "ValidSource" Then
varSourceName = objCtrl.Name
Exit For
End If
Next

If Nz(varSourceName, "") <> "" Then 'ControlSource object exists
'Concatenate the equals sign and square brackets
Me.TestCtrlSource.ControlSource = "= [" & varSourceName & "]"
Else
Me.TestCtrlSource.ControlSource = ""
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top