please experts, help!

[gilberto]

I put this thread yesterday, but there wasn't any answer so far- and solving it is quite urgent!
Does someone know how to solve this problem?

I have a combobox in a form linked to a Boolean field message_sent in a table.

There are two ways of setting it True:
- the user can click on the combobox or;
- the user can click on a button, which has a VB routine that sets message_sent = True

Sometimes, using the form, when the cursor highlights the combobox, and the user tries to click on the button, this error message appears: "You can't assign a value to this object".

Any help is really welcomed!

Thanks in advance

 

[Rob.Mills]

Is the underlying field in the table a Yes/No field? If so, I would just use a check box or an option box instead of the combo. It's easier to use and setup than the combo.

 

[gilberto]

Dear Rob
Yes it is a Yes/No field - but indeed it's already a check box (and not a combo box, as I wrote previously, sorry about that)
Is there any special setting I could use in the check box for avoiding this error?
Thanks a lot for your help!

 

[Rob.Mills]

Sounds like a problem with the code behind the button.

Paste here so we can look.

 

[gilberto]

Probably isn't the best written routine you have read Rob...
When I use the debugger step-by-step, the error never appears, so I never know where it happens.

Thanks in advance, for your help!

Private Sub PaperReceivedEmail_Click()

On Error GoTo Err_PaperReceivedEmail_Click

Dim Text_email As String
Dim Message As Integer
Dim action_aux As Integer

'checks if the submitting author has an e-mail
If IsNull(Me.[E-mail]) Then
Message = MsgBox("This person hasn't an e-mail address", 0, "Warning")
GoTo Exit_PaperReceivedEmail_Click
End If

'sets the flag for paper received and date of receiveing
Me![FlagPaperReceived] = True
Me!DatePaperReceived = Date
Me!Current_Action = 11

Text_email = "Dear " & Me.[CallTerm] & vbCrLf & "Text test" & vbCrLf & "Best wishes" & vbCrLf & "Valerie Belton"

'sends e-mail
Call SendEmail("test paper received e-mail", Text_email, Me.[E-mail])

'creates a record in paper history about the e-mail
If IsNull(Me!NumRevisionsPaperRevise) Then
action_aux = 0
Else
action_aux = Me!NumRevisionsPaperRevise
End If

Call CmdAddRecord(Me.Paper_Record, 11, "e-mail sent to author: new paper received", action_aux)

Exit_PaperReceivedEmail_Click:
Exit Sub

Err_PaperReceivedEmail_Click:
MsgBox Err.Description
Resume Exit_PaperReceivedEmail_Click

End Sub
'''''''''''''''''''''''''''''''''''''''''''''
Public Sub SendEmail(StrSubject As String, StrBody As String, StrTo As String)

Dim NameSpace As Object
Dim EmailSend As Outlook.MailItem
Dim EmailApp As Object

Set EmailApp = CreateObject("Outlook.Application") 'using the outlook object
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

EmailSend.Subject = StrSubject
EmailSend.Body = StrBody

EmailSend.Recipients.Add (StrTo) 'add the recipient
EmailSend.Display 'display the email
' EmailSend.Send 'send the email

Set EmailApp = Nothing
Set EmailSend = Nothing
End Sub

'''''''''''''''''''''''''''''''''''''
Public Sub CmdAddRecord(PaperRecNo As Integer, Action_Num As Integer, Descr_Act As String, RevNo As Integer)

Dim HistoryTable As DAO.Recordset


'Open up a new record in the table [History New] and sets the values in its fields

Set HistoryTable = CurrentDb.OpenRecordset("History New")
HistoryTable.AddNew
HistoryTable!PaperID = PaperRecNo
HistoryTable!Action = Action_Num
HistoryTable!Description = Descr_Act
HistoryTable!RevisionNo = RevNo
HistoryTable!Date = Date
HistoryTable.Update
Set HistoryTable = Nothing

Forms![Paper].Refresh

End Sub

 

[Rob.Mills]

It's not obvious to me but there's a couple places that you have Me.[E-mail]. I imagine that needs to be Me![E-Mail]

Also, when you're debugging remove the line that tells what to do when an error occurs. Yours is "On Error GoTo Err_PaperReceivedEmail_Click"

This way when you run it you'll get a debug message and it should take you straight to the problem.

Let me know how that goes.

 

[gilberto]

Hi Rob
I did what you suggest (turned off the On Error Go To), and run the button.

When I leave the cursor on the check box associated with FlagPaperReceived (a Yes/No field) and press the button, the VB error window shows the following message:

Error 2448
You can't assign a value to this object

I then pressed the Debug button in the VB error window.

It highlights in yellow the line:
Me![FlagPaperReceived] = True

Interesting that if I go over the line with the cursor, to see the value shown for this variable, it shows:
Me![FlagPaperReceived] = True = True

It seems that there is a conflict between the cursor on the check box (in the Form) and setting the variable as true in the VB code...

 

[Rob.Mills]

Well I'd be lying if I said I'm not stumped at all...

Is the name of the check box the same as the control source? They don't have to be. But if they're different and you're trying to change the value of it but using the control source instead of the name, that will cause problems.

 

[gilberto]

Hi Rob

Indeed they haven't the same name (the checkbox has the name PaperReceived_Fla).

I was wondering if I could solve this problem removing (in the code) the cursor from the check box (putting the cursor in a text box associated to the field name_of_paper in the form, for example) - do you know how I could do this please?

Thanks again.

 

[Rob.Mills]

Wait before you try something new. If the name is different then try changing that. Use

Me![PaperReceived_Fla] = True

This makes sense because it's trying to change the value of an object, not a field. You were referencing a field.

Try this first and if it doesn't do the trick then we'll try what you were talking about.

 

[gilberto]

I tried that (I didn't know it's possible to set objects, I thought we could set only fields) and I've got the error:
Run time error 2465
Microsoft Access can't find the field 'PaperReceived_Fla' referred to in your expression

 

[Rob.Mills]

That must not be the name of the checkbox then. Or maybe there's a typo. But this should work. It's a very simple step that I've done over and over. All you're doing is setting the value. Try this at last resort:

Forms!FormName!CheckBoxName.Value=True

 

[gilberto]

The error message remains, even using this one:
Me!PaperReceived_Fla.Value = True

When I try to use (the Form Paper is where the check box is placed):
Forms!Paper!PaperReceived_Fla.Value = True

a Write Conflict message appears...

 

[Rob.Mills]

Well, crap! I'm stumped. I've not been able to recreate the problem. Do you want to try emailing it to me? You'd have to email uncompressed as I don't have zip software at work.

robert.mills@zcsterling.com

 

[The_Doc_Man]

Whenever this occurs, to prevent myself from pulling out my paucity of remaining hair (which is already lonely enough...)

I always pause. Run the Analyzer>>Documenter tool on that form, tell it to list all controls and their attributes. Yes, lots of paper, but very important. Print it out. Hard copy that you can write on and dribble coffee on and other things.

OK, now go back into the offending code and find EVERY DATA REFERENCE in that code as a control on the form. When you reach a data reference for which you cannot find the control on the form or a global variable in a class or general module, you have found your culprit.

The "You can't assign a value to this object." message usually means that the object doesn't have a .Value property. Labels are like that. Options (elements of an option group) are like that, but the containing option group DOES have a value. Rectangles and lines have no .Value property. Something (other than a text box) that is unbound is also like that under some rare circumstances. The control that contains a sub-form is DEFINITELY like that.

Now, the Write Conflict message is different. That might actually represent progress of a backhanded sort. Write conflicts mean, essentially, "You can write to this, but you can't do it now." Which in turn USUALLY means that someone or something else has that same object open for write already. The someone or something else CAN be you - in another part of the same application - so don't go looking for interlopers to bash.

 

[gilberto]

hi the_doc_man

Rob helped me to solve the problem. Anyway, thanks a lot for helping me, and teaching how to search for problems using the Analyzer (which I didn't know).

Thanks again for your help, guys! (The database is already working in the network today!)