Can't assign a value to unbound textbox on Form 1

[tubbers]

I've got a form (frmSpreadsheet), which has two unbound textboxes (ctlRawDetHD1 and ctlRawDetHD2) that I am trying to populate using values from a query (CountRawDetectHD1). The ctlTagCode is bound to a table (FishCharacteristics) I get an error '2448', "You can't assign a value to this object". Here's the code I am using:

Option Compare Database

Private Sub Form_Load()
    Dim RawDetectHD1, RawDetectHD2 As Variant
    RawDetectHD1 = DLookup("HDCount", "CountRawDetectHD1", "TagCode = [ctlTagCode]")
    RawDetectHD2 = DLookup("HDCount", "CountRawDetectHD2", "TagCode = [ctlTagCode]")
    
    If (Not IsNull(ctlTagCode)) Then Me![ctlRawDetHD1] = RawDetectHD1
    If (Not IsNull(ctlTagCode)) Then Me![ctlRawDetHD2] = RawDetectHD2
End Sub

When I get the error and try to debug, If (Not IsNull(ctlTagCode)) Then Me![ctlRawDetHD1] = RawDetectHD1 is highlighted.

Any suggestions on where I've gone wrong?

 

[mordja]

tubbers,

To start with your lookups should be

RawDetectHD1 = DLookup("HDCount", "CountRawDetectHD1", "TagCode = " &[ctlTagCode])

if tag code and ctlTageCode are numeric or

RawDetectHD1 = DLookup("HDCount", "CountRawDetectHD1", "TagCode = '" & [ctlTagCode] & "'")

if they are strings. Also you might like to give [ctlTagCode] a fuller reference ie [Forms]![YourForm]![ctlTagCode].

Also you wrap the bottom lines with

If (Not IsNull(ctlTagCode))

Yet you pass it to your DLookup ??
Lastly why are you using variants ??


Mordja

 

[tubbers]

I basically took some code that I had written for another form and tried to adapt it to this situation (My VBA is very limited), which is why the things you are questioning (variables, etc.) are in place. If they're wrong, then I can change it.

Maybe if I explain exactly what I'm trying to achieve...

The query(s) count up the number of times a TagCode was detected on that specific Hydrophone, if at all. Meaning, TagCode 1234 could have been detected 5 times on Hydrophone 1 or not at all.

The form is set up to display this different characteristics of a specific TagCode and then in various textboxes there should be the results of varying queries. The CountRawDetect queries are the first ones that I've attempted so I don't even know if I'm going about this the right way.

I guess I don't need the If (Not IsNull) bit since TagCode will never be null. I tried the following code but got a different error, "Run time error '94', Invalid use of null" and this is the line that is highlighted "RawDetectHD2 = DLookup("HDCount", "CountRawDetectHD2", "TagCode = '" & [Forms]![frmSpreadsheet]![ctlTagCode] & "'")
"

Option Compare Database

Private Sub Form_Load()
    Dim RawDetectHD1, RawDetectHD2 As String
    RawDetectHD1 = DLookup("HDCount", "CountRawDetectHD1", "TagCode = '" & [Forms]![frmSpreadsheet]![ctlTagCode] & "'")
    RawDetectHD2 = DLookup("HDCount", "CountRawDetectHD2", "TagCode = '" & [Forms]![frmSpreadsheet]![ctlTagCode] & "'")
    
    Me![ctlRawDetHD1] = RawDetectHD1
    Me![ctlRawDetHD2] = RawDetectHD2
End Sub

 

[PHV]

I don't think that the bound field TagCode is already populated in the Load event.
Try to put your code in the Current event procedure of the form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

I put the code in the current event. I get the invalid use of null error again on the second Dlookup (line number 4). If I comment out lines 4 and 6, I get the original error, 2448, Can't assign a value to this object. I don't understand why the invalid use of null error only happens with the second Dlookup as it is exactly the same as the first DLookup.

Option Compare Database

1 Private Sub Form_Current()
2     Dim RawDetectHD1, RawDetectHD2 As String
3     RawDetectHD1 = DLookup("HDCount", "CountRawDetectHD1", "TagCode = '" & [Forms]![frmSpreadsheet]![ctlTagCode] & "'")

4     'RawDetectHD2 = DLookup("HDCount", "CountRawDetectHD2", "TagCode = '" & [Forms]![frmSpreadsheet]![ctlTagCode] & "'")
    
5     Me![ctlRawDetHD1] = RawDetectHD1
6     'Me![ctlRawDetHD2] = RawDetectHD2
7 End Sub

 

[PHV]

Dim RawDetectHD1, RawDetectHD2 As String
RawDetectHD1 is a Variant, so can hold null value.
RawDetectHD2 is a String, so will raise the invalid use of null error.
Seems that your DLookUp functions return null value ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

Okay, that makes sense. Any idea how to fix the cannot assign a value to this object error?

 

[PHV]

Perhaps the bound field (ControlSource) of ctlRawDetHD1 don't allow null ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

It is an unbound textbox with a general number format set up in the properties.

 

[tubbers]

I'm still trying to figure this out...would the fact that the count function is read-only be a reason why I am not able to assign that value to the the unbound text box?

 

[PHV]

the fact that the count function is read-only
Which count function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

The DLookup uses a query, CountOfRawDetectHD1:

SELECT Count(RawEchoes.Hydrophone) AS CountOfHydrophone, RawEchoes.Period
FROM RawEchoes
GROUP BY RawEchoes.Period, RawEchoes.Hydrophone
HAVING (((RawEchoes.Hydrophone)=1));

 

[PHV]

None of the DLookUp you posted should work:
RawDetectHD1 = DLookup("HDCount", "CountRawDetectHD1", "TagCode = [ctlTagCode]")
Where are HDCount and TagCode defined in your query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

Sorry, wrote the wrong code down, I've tried so many things that I have a lot of different code lying around.
Here's the right one:

SELECT Count(RawEchoes.Hydrophone) AS HDCount, RawEchoes.TagCode
FROM RawEchoes
GROUP BY RawEchoes.TagCode, RawEchoes.Hydrophone
HAVING (((RawEchoes.Hydrophone)=1))
ORDER BY RawEchoes.TagCode;

 

[PHV]

What is the data type of RawEchoes.TagCode ?
If numeric then get rid of the single quotes in the DLookUp call.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

RawEchoes.TagCode is text.

Now I'm not getting the original error (can't assign value) anymore when I open the form but I'm not getting any results in the textbox either.

Option Compare Database

Private Sub Form_Current()
    Dim RawDetectHD1 As Variant
    
    RawDetectHD1 = DLookup("HDCount", "CountofRawDetectHD1", "TagCode = '" & [Forms]![frmSpreadSheet(FC)]![ctlTagCode] & "'")
    
    Me![ctlRawDetHD1].Value = RawDetectHD1
End Sub

This is about to do my head in. Is this the best way to get the information into my form or is there another way?

 

[PHV]

And what about this ?
Me!ctlRawDetHD1 = DCount("Hydrophone", "RawEchoes", "Hydrophone=1 AND TagCode='" & Me!ctlTagCode & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

Thank you, I think we're getting closer.

At least now when I open the form, I get a value of "0" in the textbox and I wasn't getting anything before. I'm thinking that there's a problem somewhere matching the TagCode fields so I'll have to take a look at it.

 

[tubbers]

PHV,

It seems that there is some kind of flaw either in my query or in the code because the results aren't making sense.

My form is set up to display as a datasheet. When I open the form, the values in the ctlRawDetHD1 for all records are the same. i.e. if Record 1 has a tagcode of 1234, and a RawDetectHD1 count of 2, Record 2, 3, 4, etc., also has a RawDetectHD1 count of 2 - even though the RawDetectHD1 count should be a different number for each record.

I played around with some different events (Form_Open and ctlRawDetHD1_Enter) and they do the same thing.

Any ideas how to fix this? Thanks for the help.

 

[PHV]

It is standard behaviour for unbound controls in a continuous form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244