You tried to assign the Null value to a variable that is not a Variant data type. (Er

[Bee]

Hi,

I am getting this error 'You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)' I know why I am getting the error, I found out that Form and Subform should be implemented only with one-to-many relationships where the Subform is the many end. However, I have the opposite of that for a reason.

Is this bad practice? Is there anyway around it or to fix the error please?

Any help will be very much appreciated,
B

 

[boblarson]

I don't see how you can have a main form with a subform (if they are linked) with the main being the many and the subform being the one. It just isn't built to work that way. A SUBform (hint - keyword SUB) is the associated records of a one-to-many relationship. You can use it for a one-to-one relationship but it doesn't make sense.

 

[Banana]

Why would you want to use Many table or query of the table as recordsource for a form, not a subform? Did you want to search using many table or something like that?

If so, then this isn't strictly necessary to use a form/subform. Instead, I'd just opt for a continuous form with a header where you can use a combobox to enter the search criteria, and display the search results in the detail sections.

More information is needed about how you want to accomplish here...

 

[gemma-the-husky]

if you have a "many" form where you want data from a linked record that really represents the master record, you can include the "one" table in the query, and then you don't need to use a mainform/subform paradigm.

The mainform/subform setup is really a way of simplifying presentation of linked data, which can't be an issue in your case

 

[Bee]

if you have a "many" form where you want data from a linked record that really represents the master record, you can include the "one" table in the query, and then you don't need to use a mainform/subform paradigm.

The mainform/subform setup is really a way of simplifying presentation of linked data, which can't be an issue in your case

I tried using a query instead of subform, but my query was not updatable.

More information is needed about how you want to accomplish here...

I have a construction database with tblSite, tblPhase...and tblRCC. First a site is created in the tblSite which is the one side, then a number of phases will be created for that site in tblPhase using SiteID as a FK in tblPhase. Now tblRCC will have information that applies to a number of phases, so a FK is entered in tblPhase that refelects records in tblRCC.

The tables are as follows:
tblSite:
SiteID (PK)
SiteName

tblRCC:
RCCID (PK)
RCCREF

tblPhase:
PhaseID (PK)
SiteID (FK)
RCCID (FK)
RefNum

I am using a query that returns Site name and phase RefNum details in the main form and I have got RCCID (FK) also in the query to be able to link to subfrmRCC. The problem lies in the SubfrmRCC. whenever I try to enter a new record, it returns the error: "You can't assign a value to this object". The error only occurs on any first field that I attempt and only once. When I click Ok in the error message, the subform creates that record just fine.

I was thinking to trap this error and just ignore it since the database works fine, but I am afraid it may cause some problems later.

Any suggestions please,
B

 

[Banana]

Do you need to have data from tblRCC displayed? If not, then you can just discard the tblRCC from the query and it'll be updateable as the tblPhase has SiteID and can relate directly to tblSite.

Of course, you would have to be able to know the RCCID if you were going to create a new record with the query. This can be done with a combobox or listbox to keep the query updateable and if you need, implement VBA to allow for addition of RCC record to combobox or listbox.

Query can't be updateable if you have two "sibling" tables (e.g. tblSite and tblRCC) that both relates to tblPhase, as Access cannot understand how those two sibling relate to each other.

As for the error, it shouldn't show up at all, and I'd be just a tad concerned if I had that error and would want to fix this. That said, need more information... Can you tell if the error originates with one field or not? I'm suspecting RCCID is the culprit but not 100% sure. You'll need to do some more testing to see how the error appears and under what circumstances.

HTH.

 

[Bee]

Do you need to have data from tblRCC displayed? If not, then you can just discard the tblRCC from the query and it'll be updateable as the tblPhase has SiteID and can relate directly to tblSite.

Of course, you would have to be able to know the RCCID if you were going to create a new record with the query. This can be done with a combobox or listbox to keep the query updateable and if you need, implement VBA to allow for addition of RCC record to combobox or listbox.

Query can't be updateable if you have two "sibling" tables (e.g. tblSite and tblRCC) that both relates to tblPhase, as Access cannot understand how those two sibling relate to each other.

As for the error, it shouldn't show up at all, and I'd be just a tad concerned if I had that error and would want to fix this. That said, need more information... Can you tell if the error originates with one field or not? I'm suspecting RCCID is the culprit but not 100% sure. You'll need to do some more testing to see how the error appears and under what circumstances.

HTH.

The user needs to see RCC information displayed. The error is generated when I try to add a new record in SubfrmRCC and it can appear on any field that I try to use first.

It works fine when I only update one of the existing records, but the problem only occurs when adding a new record. I have searched for the error and found out that it has something to do with RCCID being in the main and sub forms, so I renamed RCCID text box in the subform. This fixed this error but it created other errors. One of them is: 'You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)'.

I am not using VBA code to update the table and I don't know why it's saying that.

 

[Banana]

Okay, that helps a bit.

I'm suspecting there's something going in out of order. Normally, I'd expect that error to show up if I was trying to create a record in junction table that did not relate to a record in the one-side table.

But in your case, Access is probably trying to add a record to junction table without the corresponding record in one-side table, which gives you the error, then updates the one-side table, consequently allows for junction record to be saved just fine.

So here's some more digging you could do:

1) create a event in subform's OnCurrent, OnDirty, BeforeUpdate, AfterUpdate, and enter in something like this:

Debug.Print RCCID

The idea is to catch exactly which event trips the error. Normally when Access trips an error (not VBA), it will do just right after an event. Therefore, if the error was created at AfterUpdate event, you'd breeze through OnCurrent, OnDirty, BeforeUpdate with no error message until you get to the error message... This will help identify which event is tripping as I"m not positively sure whether saving the record or selecting the record is causing the problem.

2) After identifying the event, use the Debug.Print to print out all variables associated on the subform. You want to see if there's any nulls or anything funky with the data. Be sure to check the main form's variable as well.

HTH.

 

[Bee]

There is not onCurrent....etc events for the subform itself. Did you mean I should trigger the RCCID text box events for example:

Private Sub RCCID_AfterUpdate()
Debug.Print RCCID
End Sub

I have tried all the events you suggested for the RCC text box, but still returns the same error. The Debug.Print does not print anything!

 

[Banana]

Silly me.

I forgot one crucial piece of information.... Toggle breakpoints at every events with "Debug.Print"

The Debug.print will print out the result to immediate windows; View -> Immediate Window.

I'm so sorry I forgot about that one...

As for subform's event, yes they do. Just click on the subform itself twice, then click on the little gray box on upper left corner. The properties window should now show "Form" in the combobox, and from there, you can see the event for subform.

 

[The_Doc_Man]

The situation you describe:

Now tblRCC will have information that applies to a number of phases, so a FK is entered in tblPhase that refelects records in tblRCC.

has a potential "gotcha" in it. If there is ANY chance that data from the phase table could have more than one related RCC entry, this is decidedly NOT normalized. As a result, you clearly are forever going to have trouble with this situation. The above quote in that case descibes a many-to-many relationship which, in Access, CANNOT be directly implemented. You need a junction table between the RCC and Phase values.

If, on the other hand, RCC is unique per phase, then the question must be whether you referenced the tables backwards. If you can have a relationship that is graphically like

Site ==one/many==> phase ==one/many==> RCC

then this should be OK. Once you select a site and a phase, RCC should be uniquely defined since phase selects RCC. BUT...

if you have the arrows running like this:

Site ==one/many==> Phases <== many/one==RCC

then you have a serious problem. Particularly if there is no relationship that defines Site ==> RCC (of any flavor, 1/1 or 1/many) so that you could make a diagram similar to

Site ==one/many==> RCC ==one/many==> Phase

If you have RCC <==many/many==> Phase then you are being caught by faulty normalization.

It is your data set. Only you can decide which of these is the case. In order to give you better advice, it would be most helpful if you could define the relationships for us in a simplified manner such as shown above.

To the new Access people who sometimes browse these forums, this is one of the examples of why you want to always scrupulously normalize your data sets up front.

 

[Bee]

lol....it's fine.

The window did not show any thing abnormal. First, it printed NULL and then the right ID numbers. I was hitting the F8 to debug when the error message came up again.

 

[Banana]

Bee, be sure to take a look at Doc_Man's suggestions first.

As for debugging, exactly under what event did you get the error? Did you insert debug.print and accompanying breakpoints in all events when you tried this?

 

[Bee]

The Doc Man

My relationships are:

Site ===one/many===> Phase <===many/one RCC. There is no chance that data from the Phase table could have more than one RCC entry because there is an RCCID in each Phase record.

Do you think this is a design problem?

 

[Banana]

The Doc Man

My relationships are:

Site ===one/many===> Phase <===many/one RCC. There is no chance that data from the Phase table could have more than one RCC entry because there is an RCCID in each Phase record.

Do you think this is a design problem?

if you have the arrows running like this:

Site ==one/many==> Phases <== many/one==RCC

then you have a serious problem. Particularly if there is no relationship that defines Site ==> RCC (of any flavor, 1/1 or 1/many)

Seems to me this is a design problem. Contrary to what you say, the Phase table can accept several instances of same RCCID, even for same SiteID.

 

[Bee]

Seems to me this is a design problem. Contrary to what you say, the Phase table can accept several instances of same RCCID, even for same SiteID.

What you are saying is what I meant.
Site ===one/many===> Phase <===many/one RCC.

Sorry about the confusion. How should the design be?

 

[The_Doc_Man]

Suddenly I'm having a brainstorm. Your database is MASSIVELY out of whack based on your description if I'm right.

By any chance, is RCC = "Rules Codes and Covenants" or something similar in concept? In other words, is RCC a fixed list of rules and laws, and each phase has a list of these with which it must comply?

If the above is true, then THIS is your REAL relational structure:

tblSites
SiteID - PK - whatever you use, maybe could be autonumbered.
Site-specific info including address, geographical coordinates, whatever you were keeping about it. Specifically includes data on township or zone in which this site lies.

The above table must be defined before you can start a new set of data entries. It is the "top" of your real structure.

tblPhases
PhaseID - PK - phase code number.
Phase-specific info about what this phase means, perhaps such titles as sub-surface plumbing, laying slab, erecting frame, running electrical services, laying floor, hanging dry-wall, erecting outer wall, roofing, finishing, ...

BUT the phase table is static. You define it according to how your company lays out its work. You can define this ahead of time. And you can use it to generate the site-specific list (see later) via VBA code or other suitable methodology. It is, in essence, a DEFINITIONS table.

tblRCC
RCCID - PK - RCC code number selecting specific code or covenant
RCC details - whatever you are tracking about a single code/covenant.

This table is ALSO static. I.e. the same codes and covenants would apply to ALL sites in a given township or zone. However, if you work in more than one zone or township, you'll have multiple entries, not all of which apply to all sites. You might have to include info on where each is applicable. You can define this ahead of time. Again, it is, in essence, a DEFINITIONS table.

This is where your design and my view diverge radically. You need some junction tables...

tblSitePhase
SiteID - FK to site table
PhaseID - FK to phase table
Other information specific to this site and phase combo - perhaps such as percent completion, start date, etc.

This tells you that for Site 81, each phase is somewhere between 0 and 100% complete, and also gives you detailed status info if that is what you wanted. If it happens that a particular subset of the phases don't apply (perhaps because some items are for all houses, others are only for two-story houses, still others are for split-level houses) then you either don't store an entry here or you include a flag that says "not applicable" or something like that. (I vote for "don't store if it doesn't apply".)

tblPhaseRCC
PhaseID - FK to phase table
RCCID - FK to RCC table
(Possible data on scope or range of applicability)

You will have one of these for each Rule/Code/Covenant that applies to work on a given phase in that township or zone. This gets filled in based on site location data, though you could do it by hand. (But if you want to know the way to make this automatic as opposed to being done by hand, ask. Don't expect an instant reply as I have other things on my plate... and besides that, it is ugly. Not impossible - just ugly.)

And here is the big kahuna:

tblSitePhaseRCC
SiteID - FK to site
PhaseID - FK to phase
RCCID - FK to RCC entry
Compliant - Y/N
Statement of discrepancy - text

This tells you if that site is compliant with all applicable codes for the given phase of work. It is site/phase specific. THIS is where you know whether your house will pass / has passed code inspection - and what is left to do if it does not pass muster.

Now, did I guess correctly as to the meaning of RCC? You might have made it easier when you explained about sites and phases if you had also defined RCC a bit better. We WANT to help you but you'll get the royal run-around (like you got above this post) if we have to grope in the dark. Take this as a VERY mild chastisement only. If you define your question better up-front, you'll get better, much faster help.

Also, one last bit of advice: You SERIOUSLY need to read up on database normalization. Access has help on this topic and you can Google-search for it, too. If you Google-search, you'll get all sorts of hits. Take only the first few from .EDU sites that you recognize by name. Ignore personal sites for the most part. You can also view a couple of .COM sites if they are for the vendors of databases - like ORACLE and Ingres come to mind.

 

[Bee]

By any chance, is RCC = "Rules Codes and Covenants" or something similar in concept? In other words, is RCC a fixed list of rules and laws, and each phase has a list of these with which it must comply?

No it's the other way around, Roads Construction Consent (RCC) has a list of phases that it applies to. Each RCC has a Reference number which applies to StartPhase n to EndPhase n e.g. 'RCCREF 999' applies from Phase 1 to phase 20.

tblSite
SiteID
(+ Site specific information)


tblPhase
PhaseID
RCCID
(+ Phase specific information)


tblRCC
RCCID
RCCREF
PhaseStart
Phase End
(+ RCC specific information)



* To save the user time selecting the RCCREF that applies to each phase one by one in the Phase form. I used VBA code that gets PhaseStart and PhaseEnd values from tblRCC, assigns them to variables and it gets the relevant RCCID and saves it in RCCID (FK) in all phases that it applies to in tblPhase.

An image is worth a thousand words, so I have attached an image of how this part of the database is structured.

* = tblGround and tblRoads use the same principle as tblRCC mentioned above.

 

[The_Doc_Man]

You will NEVER get that to work right in Access. I'm guessing that three of the four dependency arrows point the wrong way. You CANNOT possibly mean for it to have that effect. And that is why you are having so much trouble. In essence, you have created a "black hole" of relationships. Everything "sinks" into the phases table and can't get out. That is why nothing is updateable.

I suggest that this cannot correctly represent a real-world model, either. Yes, that sounds pretentious. But let me read out the English equivalent of that diagram.

Multiple phases exist for a site. The phase records also depend on RCC records, Roads Records and whatever those Grounds-Related records happen to be. ALL of them are one to many with respect to phases.

Now let me try another, simpler way to describe this problem. This is a structure with too many chiefs and not enough Indians.

In order for us to properly help you with this design, you must tell us the clear business English meaning of each table. Then and only then would ANY of us have a chance to help you with your problem. After seeing that relationship diagram, I will put hard cash money down that your structure is incorrectly related to your business model. And your structure is one that is totally intractable to Access, which is why your queries, forms, and the like are all going nuts on you.

Forgive me if this sounds harsh, because it is hard to be absolutely polite in a medium with limited capacity; and I further have only limited time to compose messages. I am being direct, which can sometimes be mistaken for harsh.

 

[Bee]

Ok here is all the business information I know about this part of the database:

The company will first buy a site (Large piece of land for construction), so they will need to create a site and store its information in tblSite. Once a certain site is created, it gets divided into a number of phases. Each phase is the size of a Street. So Phase is pretty much Street. They give each phase a number to be able to track it.

The phases are then divided into plots, each plot represents a portion of a phase (street) where a house will be constructed. But before starting the construction of houses, the company needs to get permissions from an authority to make roads (RCC)...etc. This authority will then send the permission for some of the work to go ahead. This is defined in the number of phases a certain permission will cover. For example the authority will send one permission document written on it "You now have permission to build Roads from phase number 1 to phase number 20". So, the company will build roads for phase (Street) number 1, 2, 3, 4 ... and 20 then stop and wait until the next permission is received.

I hope this explains the business logic thoroughly.

Design:
Initially, I had two designs in mind:
First:

tblSite
SiteID

tblPhase (linked to tblSite through SiteID, tblPhase is the many side)
PhaseID
SiteID

tblRCC (linked to tblPhase through PhaseID, tblRCC is the many side)
PhaseID
PhaseStart
PhaseEnd


Now, the way i thought of making the forms is as follows:

I created a master form that will show Site name and phase number at the top left corner. This information is what the customer needs to identify what Site/Phase they are dealing with. Then, there are subforms for Site, Phase, RCC, Roads...etc. Each one of them is in a tab for visual aid.

The user has to browse to the right Site and Phase, then just click the tab that applies to whatever they want to view, update.

Supposedly, the user created a site called 'Site1' (added one record in tblSite)and created three phases for 'Site1' - 'phase1', ' phase2', 'phase3' (added three records in tblPhase that have Site1 PK written in the FK fields of tblPhase). Then the user had RCC permission for Site1 starting at phase1 and finishing at phase2 (made one record in tblRCC and in the phaseStart/PhaseEnd fields the user wrote 1 in phaseStart and 2 in phaseEnd)

The problem with this is that the RCC will only appear for one phase when a query is run even though it cover phase1 and phase2.


Second:

Is the current design -

tblSite
SiteID

tblPhase (linked to tblSite through SiteID, tblPhase is the many side)
PhaseID
SiteID
PhaseID

tblRCC (linked to tblPhase through PhaseID, tblRCC is the ONE side and Phase is the MANY side)
PhaseStart
PhaseEnd

Now you are saying this is a problem. What did you mean by

you have created a "black hole" of relationships. Everything "sinks" into the phases table and can't get out.

Is it impossible because tblPhase will be queried by three different tables at the same time? I don't understand what's exactly wrong with it - is one to many/many to one not possible in Access where the junction table has more fields than just FK of the tables that it joins? Can you please be more specific in describing what's wrong and why it's wrong/impossible?