using newData

D

Debbi

Hi,

I'm having a problem that has been driving me crazy for days. I've checked
other's postings and have tried many of the suggestions to no avail.

I'm using Access 2000. In one database, I am using a form called EnterNewOrds.
It has an unbound combo box that does a record look up in the update event.
Then if not inlist it asks if you want to add it. Yes opens another form
called AddNewProp. No, resets the combo to reenter the loannum. My problem is
that when I open the new form setting newArgs to newData, I get an error
"Run-time error '-2147352567 (80020009)': You can't assign a value to this
object."
Any and all comments would be welcomed and greatly appreciated!
The code follows (forgive me if I give more than needed ;) ):

'on the EnterNewOrds form

Private Sub cboLoanNum_AfterUpdate()

Dim MyLoanNum As DAO.Recordset

If Not IsNull(Me.cboLoanNum) Then
Set MyLoanNum = Me.RecordsetClone
MyLoanNum.FindFirst "[IntPropNum]= " & Me.cboLoanNum
Me.Bookmark = MyLoanNum.Bookmark
Me.Child61.SetFocus
End If
Set MyLoanNum = Nothing

End Sub
---------------------------------------------------------------------------------------
Private Sub cboLoanNum_NotInList(newData As String, response As Integer)

Dim mbrResponse As vbMsgBoxResult
Dim strMsg As String
Dim strDocName As String

strMsg = "Loan number " & newData & " is not in the list." & _
vbCrLf & vbCrLf & "Would you like to add it?"
strDocName = "AddNewProp"

mbrResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Add new property?")

Select Case mbrResponse
Case vbYes
response = acDataErrContinue
DoCmd.OpenForm strDocName, , , , acFormAdd, acDialog, newData
response = acDataErrAdded
Case vbNo
response = acDataErrContinue
Me.cboLoanNum.Undo
Me.cboLoanNum.SetFocus
End Select
End Sub
--------------------------------------------------------------------------------------
Private Sub Form_Activate()
Me.cboLoanNum.Requery
Me.cboLoanNum.SetFocus
End Sub
================================================

'On the AddNewProp

Private Sub Close_Click()
If IsOpen("EnterNewOrds") = True Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
[Forms]![EnterNewOrds].Requery
[Forms]![EnterNewOrds]![cboLoanNum] = Me.LoanNum
[Forms]![EnterNewOrds]![cboLoanNum].SetFocus
End If
DoCmd.Close acForm, "AddNewProp"
End Sub
-----------------------------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
If MsgBox("Do you Want To Save the Current Record before Closing", _
vbQuestion + vbYesNo, "Current Record Has Not been Saved") =
vbNo Then
Me.Undo
End If
End If
End Sub
------------------------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
'<<------ The next line is where debug points to
Me.LoanNum = Me.OpenArgs
End If
End Sub
 
O

Ofer

What do you have in the ControlSource Property of the field Me.LoanNum
If you have a formula like
=(something)

Then you can't assign a value to this field, it has to be bounded to a field
in the table
========================================
Does the can edit propery of the form set to yes?
========================================
Check the record source of the form, run it without the form, to see if you
can update it
========================================


--
I hope that helped
Good luck


Debbi said:
Hi,

I'm having a problem that has been driving me crazy for days. I've checked
other's postings and have tried many of the suggestions to no avail.

I'm using Access 2000. In one database, I am using a form called EnterNewOrds.
It has an unbound combo box that does a record look up in the update event.
Then if not inlist it asks if you want to add it. Yes opens another form
called AddNewProp. No, resets the combo to reenter the loannum. My problem is
that when I open the new form setting newArgs to newData, I get an error
"Run-time error '-2147352567 (80020009)': You can't assign a value to this
object."
Any and all comments would be welcomed and greatly appreciated!
The code follows (forgive me if I give more than needed ;) ):

'on the EnterNewOrds form

Private Sub cboLoanNum_AfterUpdate()

Dim MyLoanNum As DAO.Recordset

If Not IsNull(Me.cboLoanNum) Then
Set MyLoanNum = Me.RecordsetClone
MyLoanNum.FindFirst "[IntPropNum]= " & Me.cboLoanNum
Me.Bookmark = MyLoanNum.Bookmark
Me.Child61.SetFocus
End If
Set MyLoanNum = Nothing

End Sub
---------------------------------------------------------------------------------------
Private Sub cboLoanNum_NotInList(newData As String, response As Integer)

Dim mbrResponse As vbMsgBoxResult
Dim strMsg As String
Dim strDocName As String

strMsg = "Loan number " & newData & " is not in the list." & _
vbCrLf & vbCrLf & "Would you like to add it?"
strDocName = "AddNewProp"

mbrResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Add new property?")

Select Case mbrResponse
Case vbYes
response = acDataErrContinue
DoCmd.OpenForm strDocName, , , , acFormAdd, acDialog, newData
response = acDataErrAdded
Case vbNo
response = acDataErrContinue
Me.cboLoanNum.Undo
Me.cboLoanNum.SetFocus
End Select
End Sub
--------------------------------------------------------------------------------------
Private Sub Form_Activate()
Me.cboLoanNum.Requery
Me.cboLoanNum.SetFocus
End Sub
================================================

'On the AddNewProp

Private Sub Close_Click()
If IsOpen("EnterNewOrds") = True Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
[Forms]![EnterNewOrds].Requery
[Forms]![EnterNewOrds]![cboLoanNum] = Me.LoanNum
[Forms]![EnterNewOrds]![cboLoanNum].SetFocus
End If
DoCmd.Close acForm, "AddNewProp"
End Sub
-----------------------------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
If MsgBox("Do you Want To Save the Current Record before Closing", _
vbQuestion + vbYesNo, "Current Record Has Not been Saved") =
vbNo Then
Me.Undo
End If
End If
End Sub
------------------------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
'<<------ The next line is where debug points to
Me.LoanNum = Me.OpenArgs
End If
End Sub
Click to expand...
 
D

Debbi

Hi Ofer,
Thank you for taking time to try and help.
The ControlSourse property of me.LoanNum is set to a field in a query
Property_Header.LoanNum.

The edit property for the form is set to yes.

If I open the AddNewProp form on it's own, I am able to update the record
source.
So far everything is as it should be but I still cant set the loan num from
the EnterNewOrds form.

Any other suggestions?

Thanks again,
Debbi

Ofer said:
What do you have in the ControlSource Property of the field Me.LoanNum
If you have a formula like
=(something)

Then you can't assign a value to this field, it has to be bounded to a field
in the table
========================================
Does the can edit propery of the form set to yes?
========================================
Check the record source of the form, run it without the form, to see if you
can update it
========================================


--
I hope that helped
Good luck


Debbi said:
Hi,

I'm having a problem that has been driving me crazy for days. I've checked
other's postings and have tried many of the suggestions to no avail.

I'm using Access 2000. In one database, I am using a form called EnterNewOrds.
It has an unbound combo box that does a record look up in the update event.
Then if not inlist it asks if you want to add it. Yes opens another form
called AddNewProp. No, resets the combo to reenter the loannum. My problem is
that when I open the new form setting newArgs to newData, I get an error
"Run-time error '-2147352567 (80020009)': You can't assign a value to this
object."
Any and all comments would be welcomed and greatly appreciated!
The code follows (forgive me if I give more than needed ;) ):

'on the EnterNewOrds form

Private Sub cboLoanNum_AfterUpdate()

Dim MyLoanNum As DAO.Recordset

If Not IsNull(Me.cboLoanNum) Then
Set MyLoanNum = Me.RecordsetClone
MyLoanNum.FindFirst "[IntPropNum]= " & Me.cboLoanNum
Me.Bookmark = MyLoanNum.Bookmark
Me.Child61.SetFocus
End If
Set MyLoanNum = Nothing

End Sub
---------------------------------------------------------------------------------------
Private Sub cboLoanNum_NotInList(newData As String, response As Integer)

Dim mbrResponse As vbMsgBoxResult
Dim strMsg As String
Dim strDocName As String

strMsg = "Loan number " & newData & " is not in the list." & _
vbCrLf & vbCrLf & "Would you like to add it?"
strDocName = "AddNewProp"

mbrResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Add new property?")

Select Case mbrResponse
Case vbYes
response = acDataErrContinue
DoCmd.OpenForm strDocName, , , , acFormAdd, acDialog, newData
response = acDataErrAdded
Case vbNo
response = acDataErrContinue
Me.cboLoanNum.Undo
Me.cboLoanNum.SetFocus
End Select
End Sub
--------------------------------------------------------------------------------------
Private Sub Form_Activate()
Me.cboLoanNum.Requery
Me.cboLoanNum.SetFocus
End Sub
================================================

'On the AddNewProp

Private Sub Close_Click()
If IsOpen("EnterNewOrds") = True Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
[Forms]![EnterNewOrds].Requery
[Forms]![EnterNewOrds]![cboLoanNum] = Me.LoanNum
[Forms]![EnterNewOrds]![cboLoanNum].SetFocus
End If
DoCmd.Close acForm, "AddNewProp"
End Sub
-----------------------------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
If MsgBox("Do you Want To Save the Current Record before Closing", _
vbQuestion + vbYesNo, "Current Record Has Not been Saved") =
vbNo Then
Me.Undo
End If
End If
End Sub
------------------------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
'<<------ The next line is where debug points to
Me.LoanNum = Me.OpenArgs
End If
End Sub
Click to expand...
Click to expand...
 
R

rkc

Debbi wrote:
----------------------------
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
'<<------ The next line is where debug points to
Me.LoanNum = Me.OpenArgs
End If
End Sub
Click to expand...

My guess is that it's a bang vs. dot problem.
Access thinks you're trying to set a control object to a string variable.

Try MeLoanNum = Me.OpenArgs.
 
D

Debbi

Hi rkc,

Thank you for your time. Unfortunately, that didnt work either. So, I tried
something else.

Private Sub Form_Open(Cancel As Integer)
If IsOpen("EnterNewOrds") Then
If Not IsNull(Me.OpenArgs) Then
Me.LoanNum.DefaultValue = Me.OpenArgs
End If
End If
End Sub

Then in the Before Update of AddNewProp, I reset the default value to Null
with
Me.LoanNum.DefaultValue = ""

This took care of that error but now I have another problem.
I enter in the loan number in the EnterNewOrds. It's not found so it goes to
the AddNewProp. All is fine and dandy. I add the needed info. Go to save by
the Close button click routine. It asks do I want to save from the before
Update routine. I click yes. That's when things go wrong. It pops the Not in
list msgBox that I coded and since I've added it, I click no. It closes the
AddNewProp form then I'm back at the EnterNewOrd form. The cboLoanNum now
contains the LoanNum but the form is pointing to the wrong record (actually
its the first record in the file).

Any idea on where I went wrong?

Thanks again,
Debbi
 
R

rkc

rkc said:
Debbi wrote:
----------------------------



My guess is that it's a bang vs. dot problem.
Access thinks you're trying to set a control object to a string variable.

Try MeLoanNum = Me.OpenArgs.
Click to expand...

Drat. That was supposed to read Me!LoanNum = Me.OpenArgs.
 
D

Debbi

Hi rkc,

Sorry, that didn't work either.
I commented out the original lines I had in the open routine and the update
routine in AddNewProp and tried the Me!LoanNum = Me.OpenArgs. That produced
a run-time error '2448': You can't assign a value to this object. As I
mentioned though, I did find a work around by setting the default value in
the open routine and reset the default value to null in the update routine.
But now the problem is that I'm getting the not in list from the EnterNewOrds
form when i return to it and its not setting that form correctly.

Any suggestions on why?

Thanks again! I really do appreciate yours and Ofer's help!!
Debbi
 
O

Ofer

Sorry Debbi, I should have noticed it from the begining, move the code, where
you assign the value to the On Load event of the form.
You cant assign the value on the On open event.

--
I hope that helped
Good luck


Debbi said:
Hi Ofer,
Thank you for taking time to try and help.
The ControlSourse property of me.LoanNum is set to a field in a query
Property_Header.LoanNum.

The edit property for the form is set to yes.

If I open the AddNewProp form on it's own, I am able to update the record
source.
So far everything is as it should be but I still cant set the loan num from
the EnterNewOrds form.

Any other suggestions?

Thanks again,
Debbi

Ofer said:
What do you have in the ControlSource Property of the field Me.LoanNum
If you have a formula like
=(something)

Then you can't assign a value to this field, it has to be bounded to a field
in the table
========================================
Does the can edit propery of the form set to yes?
========================================
Check the record source of the form, run it without the form, to see if you
can update it
========================================


--
I hope that helped
Good luck


Debbi said:
Hi,

I'm having a problem that has been driving me crazy for days. I've checked
other's postings and have tried many of the suggestions to no avail.

I'm using Access 2000. In one database, I am using a form called EnterNewOrds.
It has an unbound combo box that does a record look up in the update event.
Then if not inlist it asks if you want to add it. Yes opens another form
called AddNewProp. No, resets the combo to reenter the loannum. My problem is
that when I open the new form setting newArgs to newData, I get an error
"Run-time error '-2147352567 (80020009)': You can't assign a value to this
object."
Any and all comments would be welcomed and greatly appreciated!
The code follows (forgive me if I give more than needed ;) ):

'on the EnterNewOrds form

Private Sub cboLoanNum_AfterUpdate()

Dim MyLoanNum As DAO.Recordset

If Not IsNull(Me.cboLoanNum) Then
Set MyLoanNum = Me.RecordsetClone
MyLoanNum.FindFirst "[IntPropNum]= " & Me.cboLoanNum
Me.Bookmark = MyLoanNum.Bookmark
Me.Child61.SetFocus
End If
Set MyLoanNum = Nothing

End Sub
---------------------------------------------------------------------------------------
Private Sub cboLoanNum_NotInList(newData As String, response As Integer)

Dim mbrResponse As vbMsgBoxResult
Dim strMsg As String
Dim strDocName As String

strMsg = "Loan number " & newData & " is not in the list." & _
vbCrLf & vbCrLf & "Would you like to add it?"
strDocName = "AddNewProp"

mbrResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Add new property?")

Select Case mbrResponse
Case vbYes
response = acDataErrContinue
DoCmd.OpenForm strDocName, , , , acFormAdd, acDialog, newData
response = acDataErrAdded
Case vbNo
response = acDataErrContinue
Me.cboLoanNum.Undo
Me.cboLoanNum.SetFocus
End Select
End Sub
--------------------------------------------------------------------------------------
Private Sub Form_Activate()
Me.cboLoanNum.Requery
Me.cboLoanNum.SetFocus
End Sub
================================================

'On the AddNewProp

Private Sub Close_Click()
If IsOpen("EnterNewOrds") = True Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
[Forms]![EnterNewOrds].Requery
[Forms]![EnterNewOrds]![cboLoanNum] = Me.LoanNum
[Forms]![EnterNewOrds]![cboLoanNum].SetFocus
End If
DoCmd.Close acForm, "AddNewProp"
End Sub
-----------------------------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
If MsgBox("Do you Want To Save the Current Record before Closing", _
vbQuestion + vbYesNo, "Current Record Has Not been Saved") =
vbNo Then
Me.Undo
End If
End If
End Sub
------------------------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
'<<------ The next line is where debug points to
Me.LoanNum = Me.OpenArgs
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Debbi

Ofer,

Thank you for your time. Unfortunately, that didnt work either. So, I tried
something else.

Private Sub Form_Open(Cancel As Integer)
If IsOpen("EnterNewOrds") Then
If Not IsNull(Me.OpenArgs) Then
Me.LoanNum.DefaultValue = Me.OpenArgs
End If
End If
End Sub

Then in the Before Update of AddNewProp, I reset the default value to Null
with
Me.LoanNum.DefaultValue = ""

This took care of that error but now I have another problem.
I enter in the loan number in the EnterNewOrds. It's not found so it goes to
the AddNewProp. All is fine and dandy. I add the needed info. Go to save
using
the Close button click routine. It asks do I want to save from the before
Update routine. I click yes. That's when things go wrong. It pops the Not in
list msgBox that I coded and since I've added it, I click no. It closes the
AddNewProp form then I'm back at the EnterNewOrd form. The cboLoanNum now
contains the LoanNum but the form is pointing to the wrong record (actually
its the first record in the file). The new loannum is in the combo but if I
hit enter, the focus remains in the header and switches between the 2 combo's
there (client & loannum).

Any idea on where I went wrong?

Thanks again for all of your help,
Debbi

Current Code:
***** EnterNewOrds (MainForm)

Private Sub cboClient_AfterUpdate()
Me.cboLoanNum.Requery
End Sub
----------------------------------------------------------------------------------------
Private Sub cboLoanNum_AfterUpdate()
Dim MyLoanNum As DAO.Recordset
If Not IsNull(Me.cboLoanNum) Then
Set MyLoanNum = Me.RecordsetClone
MyLoanNum.FindFirst "[IntPropNum]= " & Me.cboLoanNum
Me.Bookmark = MyLoanNum.Bookmark
Me.Child61.SetFocus
End If
Set MyLoanNum = Nothing
End Sub
-----------------------------------------------------------------------------------------
Private Sub cboLoanNum_NotInList(newData As String, response As Integer)
Dim mbrResponse As vbMsgBoxResult
Dim strMsg As String
Dim strDocName As String
strMsg = "Loan number " & newData & " is not in the list." & _
vbCrLf & vbCrLf & "Would you like to add it?"
strDocName = "AddNewProp"
mbrResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Add new property?")
Select Case mbrResponse
Case vbYes
response = acDataErrContinue
DoCmd.OpenForm strDocName, , , , acFormAdd, acDialog, newData
response = acDataErrContinue
Case vbNo
response = acDataErrContinue
Me.cboLoanNum.Undo
Me.cboLoanNum.SetFocus
End Select
End Sub
----------------------------------------------------------------------------------------
Private Sub Form_Activate()
Me.cboLoanNum.Requery
'Me.cboLoanNum.SetFocus
End Sub
----------------------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
cboClient.SetFocus
End Sub
==================================================
***** AddNewProp (called Form)
Private Sub Close_Click()
If IsOpen("EnterNewOrds") = True Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
[Forms]![EnterNewOrds].Refresh
[Forms]![EnterNewOrds].Requery
Forms!EnterNewOrds!cboLoanNum.Requery
[Forms]![EnterNewOrds]![cboLoanNum] = Me.IntPropNum
End If
DoCmd.Close acForm, "AddNewProp"
End Sub
------------------------------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
If MsgBox("Do you Want To Save the Current Record before Closing", _
vbQuestion + vbYesNo, "Current Record Has Not been Saved") =
vbNo Then
Me.Undo
End If
End If
Me.LoanNum.DefaultValue = ""
End Sub
-----------------------------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
If IsOpen("EnterNewOrds") Then
If Not IsNull(Me.OpenArgs) Then
Me.LoanNum.DefaultValue = Me.OpenArgs
End If
End If
End Sub
=====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NotInList not firing ? 2
Help With Code Please 5
Not in list warning 2
Not in list warning 2
NotInList... NewData only working for some controls but not others 3
Update Combo Box when Not In List and update related info in form 1
Implementing public VBA function in Access 2003, passing multiple parameters 5
Not In List help needed 1

Top