Unbound Text Boxes in Report

[gsrajan]

Dear Sir,

I have 10 unbound textboxes with the Tag Name "LoopID" in my report. I have to display the "PatientNumbers" field from the table tblPatient in those text boxes. Below is my code. When I run the report, I get the error message: Run-time error '2448.' You can't assign value to this object and the code "Ctl.Value = rst!LCANumber" is highlighted. Please let me know what I am doing wrong.

Thank you.

Private Sub Report_Current()
Dim strSql As String
Dim dbs As Database
Dim rst As Recordset
Dim x As Integer
Dim Ctl As Control
strSql = "Select PatientNumber from tblPatient"
Set dbs = CurrentDb()
Set rst = db.OpenRecordset(strSql)
With rst
.MoveFirst
Do While Not rst.EOF
For Each Ctl In Reports!rptPatient.Controls
If Ctl.ControlType = acTextBox Then
If Ctl.Tag = "LoopID" Then
Ctl.Value = rst!PatientNumber
Exit For
End If
End If
Next Ctl
.MoveNext
Loop
.Close
End With
End Sub

 

[pbaldy]

Does the textbox have a control source that starts with an equals sign?

 

[gsrajan]

No, all 10 text boxes are unbound.

 

[Frothingslosh]

Unbound simply means they're not tied to a field in the underlying recordset. Unbound fields contain formulas all the time.

 

[gsrajan]

Thank you. No, they do not have control sources with = signs.

Thanks for your help

 

[pbaldy]

They aren't locked, or the form set to not allow edits, or something? Can you post the db here?

 

[gsrajan]

No they are locked pbaldy. they are not locked. allow edits is set to true. But the report's control source is another query. Is that a problem? My company wont allow me to send the DB. Sorry for that and please understand me. Thanks.

 

[gsrajan]

Sorry, please read as they are not locked.

 

[pbaldy]

Oh, just noticed the event. Try the format event of the section containing the textboxes.

 

[gsrajan]

Thanks, I moved my code to the details section format event, not working.

 

[pbaldy]

Well, I'm stumped. I can understand the company not wanting the db posted, but can you put together a sample db that exhibits the error?

 

[Frothingslosh]

Here's a question. When you run it and it goes into debug mode, what result do you get when you type in the immediate window:

debug.print rst!PatientNumber

If you get a blank line, try

debug.print "*" & rst!PatientNumber & "*"

If the result of that is **, then check to see if you have a null value in rst!PatientNumber. It's possible you're trying to assign Null and Access is rejecting it.

 

[gsrajan]

debug.print rst!PatientNumber show me the patient number "AS45362"

Thank you

 

[Frothingslosh]

Okay, might be time to brute force it.

I notice that 'Value' does not come up as a valid option in IntelliSense (at least in Access 2003). So let's try this:

Add a variable 'Txt' to your declarations, and make it a TextBox.
In your loops, once you've determined that the control is both a text box and is appropriately tagged, replace

Ctl.Value = rst!PatientNumber

with

Set Txt = Ctl
Txt.Value = rst!PatientNumber
Set Txt = Nothing

It's brute force, but it may work.

 

[gsrajan]

Thanks for trying. There is no error message, and not working.

 

[Frothingslosh]

No error message at all?
What do you get if you toss debug.print txt.value after Txt.Value = rst!PatientNumber?

Edit: After thinking about it on the way home, if that's correct that you're no longer getting error messages with my suggestion, then try putting Txt.Refresh after the Txt.Value line. You may just need to refresh the control after the update.