Results 1 to 11 of 11
  1. #1
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: attempt to assign a null value to variable?? (a2k3

    I find this confusing. Which form or subform contains cboFind or are there two of them? You are referring to Me.cboFind, but then you refer to Forms!frmConOrg!fsubOrg.Form!cboFind.SetFocus. Where is this code running? Are these two different comboboxes? If cboFind is on the subform, you don't have to use a full reference to set the focus to it. If it's on the parent form, then the NewRecord is for the parent form not the subform. Can you clarify please?
    Charlotte

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: attempt to assign a null value to variable?? (

    Procedure runs on cboBox on the main form. The set focus refers to a second cboBox this one is on the subform. Could this procedure be causing the "assigning a null value to a variable" error?

    E

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    attempt to assign a null value to variable?? (a2k3

    I'm working with a main form with 5 subforms. The main form is based on a junction table. Using the NIL code below, I'm using the NIL event on an unbound combo box on the main form to lookup up an existing record or create a new one. So far so good.

    However, in new records when I attempt to enter data in any of the controls on any of the subforms I get the following message: "You tried to assign the Null value to a variable that is not a variant data type."

    What variable would that be and now I would be attempting to assign a Null value? I've examined the code behind the main form and the subforms and I don't have the knowledge needed to see where I've attempted to assign a null value. I've attached the NIL code attached to the main form's lookup combo hoping this the right place to start.


    Private Sub cboFind_NotInList(pstrNewData As String, pintResponse As Integer)
    ' Comments :
    ' Parameters: pstrNewData
    ' pintResponse -
    ' Modified :
    '
    ' --------------------------------------------------

    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    MsgBox "Record doesn't exist. Creating new record."
    Me.cboFind.Undo
    'Create a new record
    DoCmd.GoToRecord , , acNewRec
    'set focus to the subform and then the cboFind control, requires two statements
    Forms!frmConOrg!fsubOrg.SetFocus
    Forms!frmConOrg!fsubOrg.Form!cboFind.SetFocus
    'Tell Access you've discarded the new value
    pintResponse = acDataErrContinue


    'TVCodeTools ErrorHandlerStart
    PROC_EXIT:
    Exit Sub

    PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

    End Sub

    Eli

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: attempt to assign a null value to variable?? (

    You're moving to a new record on the main form. That means that you haven't created a new record on the main form before you try to create a new record on the subform. That probably is what's causing the problem. Did you really mean to move to a new record on the main form? If so, why are you setting the focus to the subform?
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: attempt to assign a null value to variable?? (

    I'm probably doing this the hard way but this is what I had in mind...

    The main form only has three controls: lngConOrgID; lngContactID; lngOrgID. If cboFind doesn't find the lngConOrgID, I want to create a new record and go to the Find control on the Org subform. If the Org sexistings then select it, if not enter it. Then repeat the process with the Contact subform.

    This is probably a rather convoluted approach. I've really been struggling to find a simple workable approach to the one person with potentially many organizations, one organization with likely many contacts situation.

    I'll try reworking the code and let you know what happens. In the meantime, if you're in a position to smite me with a little wisdom and experience about best approaches, I'd really love it.

    E

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: attempt to assign a null value to variable?? (

    OK, check me on this: You look up a contact organization ID in the main form, meaning I take it, that you are checking for the contactID and the OrgID in the table? If that record doesn't exist, you want to go to the subform and create a new organization record? But isn't the main form based on a join table? What is the subform based on? How are the main form and subform related? Did you use master/child links?
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: attempt to assign a null value to variable?? (

    I look up a contact organization ID in the main form, checking to see if I have an entity that combines that person and org. If that identity doesn't exist, I want to create a new record in tblConOrg with an autonumber ID and proceed to add the lngOrgID and the lngContactID to complete the record.

    I think I am on track up to creating the new record but then I self-destruct. After generating the new record I should be proceeding to the next control cboOrgID on the main form, rather than moving to the subform. Assuming the OrgID I need is not an existing record, how do I move from cbOrgID to fsubOrg where I can create the new record. I can't figure out how to do that using the NIL event.

    E

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: attempt to assign a null value to variable?? (

    I still am confused. If you don't have a contact-organization ID, does that mean you don't have a record for the organization? How do you handle multiple contacts for the same organization? Can you post a stripped down version of your database with only the necessary form, subforms and tables/queries so we can take a look and see what's actually going on?
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: attempt to assign a null value to variable?? (

    Charlotte,

    Hope this clarifies and communicates more effectively than I've managed.

    E

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: attempt to assign a null value to variable?? (

    Since you used the same names for org/office and first name/last name combinations, it's a little hard to tell which one you're dealing with. However, your problem is that you need to be able to create a new contact record or a new organization record and you can't do it from the join table, you have to have a means of doing it outside the form/subforms, then populate the form and its subforms with the value for the new selection. Your subforms are linked to the parent form, so you can't go to a new parent form record and then create records in the subforms for organization or for contact because the there is no record for them to link to. I usually handle something like this by trapping the NIL of the combobox and popping up a dialog form to allow them to enter the required info for the new record, then I requery the combobox to make sure the item is now in the list and set the value to that new item. Otherwise, you're not going to be able to make this design work.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: attempt to assign a null value to variable?? (

    Thanks. That's exactly what I needed to know.

    E

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •