Invalid Use of Null (1 Viewer)

[svtguy02]

Hi there

I'm currently running into an issue on a combo box selector form being naughty, so to speak. The purpose of the form is to be able to select a customer or a facility, that is filtered by customers
(Customers and Facilities are a 1 - many with 1 customer to many facilities)
When you select a customer it filters out the facilities you can choose to ONLY facilities related to the customer chosen. This forms purpose is to be able to edit customer or facility records. I want to be able to select JUST a customer (no facility) click continue, and it opens up the associated customer record, or if I choose a Facility I'd want it to open up the associated facility record.

Here are some shots of what the form looks like so you can get a better visual:

ONLY Customer is selected here:

Customer AND Facility are selected here:

My code is as follows:

Private Sub BtnContinue_over_Click()
On Error GoTo Err_BtnContinue_over_Click

Dim stDocCust As String
Dim stDocFac As String
Dim stLinkCriteria As String
Dim CustInt As Integer
Dim FacInt As Integer

stDocCust = "Customers"
stDocFac = "FacilityInfo"

If Me.CustCmbo_Customers <> "" And Me.CustCmbo_Facilities = Null Then
DoCmd.OpenForm stDocCust, , , Me.CustCmbo_Customers = Forms!Customers![Customers-CustName]

ElseIf Me.CustCmbo_Customers <> "" And Me.CustCmbo_Facilities <> "" Then
DoCmd.OpenForm stDocFac, , , Me.CustCmbo_Facilities = Forms!FacilityInfo![FacilityInfo-FacilityName]

End If

Exit_BtnContinue_over_Click:
Exit Sub

Err_BtnContinue_over_Click:
MsgBox Err.Description
Resume Exit_BtnContinue_over_Click

End Sub

This is the code that does the filtering:

Private Sub CustCmbo_Customers_Change()

Dim q As String
Dim sel As Integer

q = "SELECT FacilityID, Facility_Name FROM Facilities WHERE CustomerID = " & Me.[CustCmbo-Customers].Column(0) & " ORDER BY Facility_Name"

Me.[CustCmbo-Facilities].RowSource = q
Me.[CustCmbo-Facilities].BoundColumn = 1
Me.[CustCmbo-Facilities].Requery

End Sub

Any help is apprecitated once again

 

[Steve R.]

I think you need to re-write the code:

 If Me.CustCmbo_Customers <> "" And Me.CustCmbo_Facilities = Null Then
DoCmd.OpenForm stDocCust, , , Me.CustCmbo_Customers = Forms!Customers![Customers-CustName]

to

 If isnull(Me.CustCmbo_Customers)  And isnull(Me.CustCmbo_Facilities) Then
DoCmd.OpenForm stDocCust, , , Me.CustCmbo_Customers = Forms!Customers![Customers-CustName]

You should also test for the empty string situation.

 

[svtguy02]

Alright well I've figured that part out. I've written the following which works:

If CustInt <> "" And IsNull(Me.CustCmbo_Facilities) Then

That being said..... instead of geting "Invalid Use of Null" now.... I'm getting "Type Mismatch" as per the line RIGHT after it!

DoCmd.OpenForm stDocCust, , , Forms!Customers![Customers-CustName] = Me.CustCmbo_Customers

What could be wrong!?!? Heres the whole thing:

    Dim stDocCust As String
    Dim stDocFac As String
    Dim stLinkCriteria As String
    Dim CustInt As Integer
    Dim FacInt As Integer

    stDocCust = "Customers"
    stDocFac = "FacilityInfo"
    CustInt = Me.CustCmbo_Customers
    'FacInt = Me.CustCmbo_Facilities

    'DLookup
    If CustInt <> "" And IsNull(Me.CustCmbo_Facilities) Then
        DoCmd.OpenForm stDocCust, , , Forms!Customers![Customers-CustName] = Me.CustCmbo_Customers

    ElseIf Me.CustCmbo_Customers <> "" And Me.CustCmbo_Facilities <> "" Then
        DoCmd.OpenForm stDocFac, , , Forms!FacilityInfo![FacilityInfo-FacilityName] = FacInt
        'DoCmd.OpenForm stDocFac, , , Me.CustCmbo_Facilities = Forms!FacilityInfo![FacilityInfo-FacilityName]

    End If

Type mismatch is really pissing me off becuase it was able to pass the Me.CustCmbo_Customers value over to the Customers form using OpenArgs. I confirmed that with a txt box in the 2nd form that displays Msgbox Me.OpenArgs.... I just don't get it!

 

[Steve R.]

When I use comboboxes, I usually have two columns, one is a unique numeral and the other is a text string. All searches and operations are actually done using the unique numeral. However, all the user sees is the string value. I suspect that you may be "mixing" the numerical value and the string value.

Check your combobox properties and see if you have two columns where the numerical column happens to be hidden.

 

[unclejoe]

‘First condition – open customer form only
If Not IsNull(Me.CustCmbo_Customers) And IsNull(Me.CustCmbo_Facilities) Then
‘open the form Customer

‘second condition – open facility if both not null
ElseIf Not IsNull(Me.CustCmbo_Customers) And Not IsNull(Me.CustCmbo_Facilities) Then
‘open the form Facilities

‘third condition – open facility if customer is null
ElseIf IsNull(Me.CustCmbo_Customers) And Not IsNull(Me.CustCmbo_Facilities) Then
‘Open the form Facilities

You have an incorrect “Where” condition filter. It should be like….

DoCmd.OpenForm stDocCust, , , “Customers = ‘” & Me.Customer_Name & “’”
DoCmd.OpenForm stDocFac, , , “Facilities = ‘” & Me.Facility_Name & “’”

“Customer” and “Facilities” is the field/column name in the table. I assume the “customer” and “facilities” are the field name, change it if it is not the field name.

I assume the Form “Customer Combo Form” is the search form and the controls are “Customer_Name” and “Facility_Name” are the names of the control which the values are use for filtering the forms.

PS- You're not using the openargs.

Hi there snip...
End Sub
Any help is apprecitated once again

 

[svtguy02]

Let me clarify the objects listed on this form

FormName - "Customers Combo Form"

Customers Dropdown - CustCmbo-Customers
Facilities Dropdown - CustCmbo-Facilities
Continue Button - BtnContinue
Cotninue Button Hover - BtnContinue_over

By selecting a Customer from CustCmbo-Customers and clicking BtnContinue_over it should bring me to the Customers form opening the related customer record

By Selecting a customer from CustCmbo-Customers AND a Facility from CustCmbo-Facilities and clicking BtnContinue_over it should bring me to the Facilities form opening the related facility record

There will never be an instance where you select JUST a facility, and NOT a customer due to the fact that you need to know what customers facility you are dealing with


And yea, I know I'm not using openargs... I was actually tying to use the WHERE variant of the OPENFORM method.

So that it will open the customers form WHERE CustomerID = Me.CustCmbo_Customers hence the following:

    Dim stDocCust As String
    Dim stDocFac As String
    Dim stLinkCriteria As String
    Dim CustInt As Integer
    Dim FacInt As Integer

    stDocCust = "Customers"
    stDocFac = "FacilityInfo"
    CustInt = Me.CustCmbo_Customers

    If CustInt <> "" And IsNull(Me.CustCmbo_Facilities) Then
        [B]DoCmd.OpenForm stDocCust, , , Forms!Customers![Customers-CustName] = Me.CustCmbo_Customers[/B]

    ElseIf Me.CustCmbo_Customers <> "" And Me.CustCmbo_Facilities <> "" Then
[B]        DoCmd.OpenForm stDocFac, , , Forms!FacilityInfo![FacilityInfo-FacilityName] = Me.CustCmbo_Facilities[/B]

Customers-CustName is a field in the Customers form that is bound to Customer_Name which is a text field
FacilityInfo-FacilityName is a field in the Facilities form that is bound to FacilityName which is a text field.

I may have just answered my own question there considering the value it is passing is a numerical value trying to pass to a text field haha... Any input?

 

[unclejoe]

There will never be an instance where you select JUST a facility, and NOT a customer due to the fact that you need to know what customers facility you are dealing with

But, that’s not what you said in your first post…

“or if I choose a Facility I'd want it to open up the associated facility record.”

It is not clear of what you’re trying to do. Did your code work?

DoCmd.OpenForm stDocCust, , , Forms!Customers![Customers-CustName] = Me.CustCmbo_Customers

Or

you’re trying to insert a new record with the “CustCmbo_Customers” into the textbox in the form “Forms!Customers![Customers-CustName]?

In short, auto fill a new record with the select combos?

 

[svtguy02]

When you load that form, the Facility combo box has no options in it until you select a customer. Once the customer is selected it then shows facilities that are related to the customer chosen in the Customers Combo box

Thats how its possible to "never ever" choose a facility before a customer, due to the fact that trying to choose a facility before choosing a customer will yield in no options being available for that facility. Does that make sense to you? If you want any more clarification please let me know as I'm still tryin to figure this one out! Thank you

 

[ajetrumpet]

I have a question here; is the following code of yours functioning the way you want it to right now...???

stDocCust = "Customers"
stDocFac = "FacilityInfo"

If Me.CustCmbo_Customers <> "" And Me.CustCmbo_Facilities = Null Then
DoCmd.OpenForm stDocCust, , , [color=red]Me.CustCmbo_Customers = Forms!Customers![Customers-CustName][/color]

ElseIf Me.CustCmbo_Customers <> "" And Me.CustCmbo_Facilities <> "" Then
DoCmd.OpenForm stDocFac, , , [color=red]Me.CustCmbo_Facilities = Forms!FacilityInfo![FacilityInfo-FacilityName]

[/color]

Are the lines in red filtering the popup forms to one record like they should be???

 

[svtguy02]

not at all.... and thats the issue I'm looking to resolve.

When I try using Openargs to pass the CustomerID or FacilityID over, on the form it opens on "Form_Load()" event I have the following:


If Me.OpenArgs <> "" Then

DoCmd.GoToRecord , , acGoTo, Me.OpenArgs

End If

I would like it to DoCmd.GoToRecord goto, OPENARGS (Whatever the value may be)..... but it doesnt. IT just pulls up the first record like it would if I opened the form normally.



IF I use the WHERE clause as part of the DoCmd.OpenForm I would THINK that its like an SQL Statement and I could use "WHERE CUSTOMERID = CUSTOMERID" kinda thing..... because in the combo form you select a customer from the combo box (which has an ID) and I want it to open that Customers record in the Customers form.

So... if CustomerID = 4 in the Customers Combo Box.... by clicking CONTINUE I want to open CustomerID 4's record. Comprende? don't hesitate to ask for clarification, I'll provide whats needed in terms of clarification to get this problemo solved! Thank you all once again...

 

[gemma-the-husky]

Is this sorted yet?

in this bit of code you are testing custint against a string - is this where your type mismatch is coming from

surely its something like

if nz(custint,0)<>0 etc etc




'DLookup
If CustInt <> "" And IsNull(Me.CustCmbo_Facilities) Then
DoCmd.OpenForm stDocCust, , , Forms!Customers![Customers-CustName] = Me.CustCmbo_Customers

 

[ajetrumpet]

I have just tested this, and the following proved to be true...

Syntax that was read and functioned correctly...

DoCmd.OpenForm "Form", , , "[field] = forms!form!control"

Syntax that did not work...

DoCmd.OpenForm "Form", , , [field] = forms!form!control

Another thing that you should note. This...

[color=red]Me.CustCmbo_Facilities = Forms!FacilityInfo![FacilityInfo-FacilityName]

[/color]is not setting the target for the filter. Instead of setting a specific field for the target of the filter, you have set "Me.CustCmbo_Facilities as the target (entity to be filtered). The combo doesn't need to filtered, the TABLE does.

Also, a lot of times Access will "autocorrect" inconsistent spacing in VBA lines, especially when you do not enclose parts of statements in quotes. One of the reasons your statement won't work is because of the first SPACE before the equal sign. That will either result in "Invalid Use of Null", or "Field cannot be referenced by Access". So, try using the first type of syntax here, and see it it fixes the problem.

 

[unclejoe]

So, you’re saying that the combo boxes must be fill with data, right?

And if the User selects a customer and this customer has no record in the Facility. The “Facility Info” form will popup and ask the User to input this customer into the “Facility” Table with the information matching the “Customers” Table?

And if the User selects Facility, but there is no customer info, the “Customers” form will popup asking the User to input this customer into the “Customers” Table with the information matching the “Facility” Table?

And if both the combo boxes has data, and when the user click on the “Continue” button, the “Customer Combo Form” will go to a new Page?

We know the “Customer Combo Form” is a search form. What you did not said is “What is the two forms for used for?” ("Customers" and "FacilityInfo")

We need to have a better view of what are the two forms used for and why are you loading unnecessary records into the forms and it appears that there are holes in your logic hence, I’m asking “Customer Combo Form” will go to a new Page?” or it just simply opening “Facility Info” form.

Remember, if there is no information in the tables, even with the “Openargs” or “Where” filter, you’ll not be able to pull any records into forms.

When you load that form, the Facility combo box has no options in it until you select a customer. Once the customer is selected it then shows facilities that are related to the customer chosen in the Customers Combo box
Thats how its possible to "never ever" choose a facility before a customer, due to the fact that trying to choose a facility before choosing a customer will yield in no options being available for that facility. Does that make sense to you? If you want any more clarification please let me know as I'm still tryin to figure this one out! Thank you

 

[svtguy02]

Sorry guys, I'm just really bad at explaining how things work. Ive explained it as best I can but that isnt working. To resolve that, I'll just upload the forms I'm talking about so you can ALL take a look at what I have, how it works etc etc...

the Customers form is intended for adding customer information
the Facilities form is intended for adding facility information

The Customers Combo Form is an add/edit for linking to both customers and facilities.

Open Customers Combo Form and you will see ADD CUSTOMER and ADD FACILITIY which just opens to a new record

If you click the down arrow for customers you will see a list of customers
after selecting a customer the facilities drop down will update itself to show ONLY facilities related to that customer.

If you click the down arrow for Facilities WITHOUT choosing a customer you will see NOTHING.

What I want is by selecting a customer, and ONLY a customer it opens THAT customers record in the customers form so I can edit it for example
If I select a customer AND a facility, I'd want it to open up THAT Facilitys' record in Facilities form for editing. Hopefully that makes sense


Find out for yourself and tell me if you can solve my problem

 

[ajetrumpet]

I think the reason you can't open the form at the specified record is because the program cannot find the value of the "CustCmbo CUSTOMER" when you refer to it. You have a multi-column combo box there, and when you reference it, I think it is reading the entire record that is sitting in there, which would consist of two columns.

Anyway, that's just an FYI.

I eliminated one of the columns in the form's combo, and it works find with the appropriate statement in the "WHERE portion" of the DoCmd.OpenForm Command.

 

[ajetrumpet]

Here is an example that is structured like your DB. The joined field is CustomerID. The first form "Customers" will pop up just fine, but the "Facilities" form will not, because there is not enough criteria to reference (e.g. - enough FIELDS to filter on). I noticed that your cascades used the "column" property too. If you open a form with a DoCmd command and put...

WHERE [field] = Forms!FormName!ControlName.Column(0)

in the "WHERE" section, I don't think it will work too well. So, you will have to get rid of that. To filter the popup form on just ONE record, you need to reference objects for each field, so maybe you could put a separate object on your form that populates the second column from your "Facilities" table (the column with the value that JOINS your two tables together) and HIDE it. That way, you can reference the combo box and the other hidden control on the form in the criteria section of the "Facility" DoCmd Command. I have put a separate form in the file to show you what I mean. It works fine, so maybe that is the route to go..??

In the "OnClick" code for the button, I showed you where the criteria cannot be finished because the joining field of the two tables cannot be referenced correctly. I marked the line with ???????? marks.

 

[unclejoe]

Sorry guys, I'm just really bad at explaining how things work. Ive explained it as best I can but that isnt working. To resolve that, I'll just upload the forms I'm talking about so you can ALL take a look at what I have, how it works etc etc...

the Customers form is intended for adding customer information
the Facilities form is intended for adding facility information

The Customers Combo Form is an add/edit for linking to both customers and facilities.

We know this already.

Open Customers Combo Form and you will see ADD CUSTOMER and ADD FACILITIY which just opens to a new record

If you click the down arrow for customers you will see a list of customers
after selecting a customer the facilities drop down will update itself to show ONLY facilities related to that customer.

If you click the down arrow for Facilities WITHOUT choosing a customer you will see NOTHING.

See the sample in the attached file where the code I have added to AutoFill the Facility Combo box.

What I want is by selecting a customer, and ONLY a customer it opens THAT customers record in the customers form so I can edit it for example
If I select a customer AND a facility, I'd want it to open up THAT Facilitys' record in Facilities form for editing. Hopefully that makes sense

Contradition here, if you want to AutoFill the Facility combo box, Customers with a Facility will show up the Facility Combo box and Customer without Facilty will not show up.

If User click Continue button with both combo box with data, “Facility Info” will open.

If User click Continue button with only a “Customer” without a Facility ID, the “Customers” form will open up.

So, if a Customer with a Facility will always open the “Facility Info” form.

If you want the User to open just the “Customers” form only, then just remove the autofill part of the code. (But read the warning part)

Warning!
Because the form and controls is unbound to any recordsource, the combo box value will remain even the User deletes the field in the combos. Read the comments in the OnChange event in the “CustCmbo_Customers_Change”.

This is where I believe you are having problems understanding why is the form not opening to the record or not showing up.

Now comes the nonsensical part of your logic again.

I do not see the logic of Opening the “Customers” form, why? There is nothing in the “Customers” form to link the Facilty table (CustomerID column). So, there is nothing. Only just to add or edit the customer information.

So, what are you looking for? A NotInList event where the customer is not in the “Customers” table?

You’re better off using A Main Form “Customers” and Subform with “Facilities”. Just by filtering this form with one field “CustomerID”. With this CustomerID, go to the subform to insert Facilty information in the table. That’s it, nothing complicated.

Note: I have change the “Facility” combo RowSource, where you have not change during the renaming of your controls. You must do this check if you have change the name of the controls.

Sorry guys, snip...

 

[DCrake]

Simple Software Solutions

Sorry for joining this issue so late on. I read the first few threads and decided on the best action, well in my experience it works well.

1st
Create Public Variables

StrMyCompany as string
StrMyLocation as string

2nd
Create two functions

Public Function GetMyCompany() as string

GetMyCompany = StrMyCompany

End Function

3rd
Repeat the above for location

4th
In you form on the AfterUpdate Propertes of you combo boxes enter the following:

StrMyCompany = Me.ComboCompany
StrMyLocation = Me.ComboLocation

5th

Design the form that contains the data.
If the record source is a table then change it to a query containing all the records from the table.


6th
On the criteria line under the company field name in the query Enter =GetMyCompany()

Repeat for the location field.

Now when the form opens it gets the parameter for each selection passd to the Public Variables set in the first form using the GetMy... functions.

On the OnLoad procedure of the first form set the publics to "Is Not Null", therefore if the user does not make a selection then the phrase Is Not Null is passed to the query where condition.

Play around with this and see if it works.

This is ideal for users who run queries based on information collected from a form. I found in the past that newbies always referred to forms and fields in their queries making them unique to the form. So if they wanted to run the same query from a different form they had to create a duplicate query but with different where conditions. By passing the parameters to public variables from anywhere in the mdb it means that the user only needs to create one query.

Hope this makes sense.

Code Master

 

[ajetrumpet]

This is ideal for users who run queries based on information collected from a form. I found in the past that newbies always referred to forms and fields in their queries making them unique to the form. So if they wanted to run the same query from a different form they had to create a duplicate query but with different where conditions. By passing the parameters to public variables from anywhere in the mdb it means that the user only needs to create one query.

Very good information. Hopefully, it's not too tough too follow.