Table Relationships for a Newbie!

[dgaletar]

Hello. I have encountered a problem that has frustrated me for going on two days now. And the worst part is that I KNOW the answer is so simple!

I have created two tables (see image). One contains the vehicles main details, and the other the list of service procedures that have been performed for that vehicle.

I am trying to open the "Service" form from the "Vehicle Details" form as a "New Record" relating to THAT SPECIFIC VEHICLE.

I have designed everything beautifully, but I can not seem to get it to work right!!!

Every time I click the button on the "Vehicle Details" form to open a new record specific to that vehicle, it gives me an error: Index or primary key cannot contain a Null value.

When I remove the Primary Key from the "Service" table, it works but then the "CUA#" field in the "Service" table is empty, so the records are not related.

WHAT AM I DOING WRONG???

Thanks,

dgaletar

[Rabbit]

Please post the code that runs on that button click.

[dgaletar]

{Macro}
OpenForm
    Form Name...Service : Form
        View...Form
    Filter Name...
    Where Condition...[CUA#]=[VEHICLES2 subform].[Form]![CUA#]
    Data Mode...Add
    Window Mode...Normal

[Rabbit]

Oh, you're using a macro. I don't use macros but I can tell you what I would do in VBA code. I would open the form and pass the CUA# in the OpenArgs parameter. Then, in the on load event of the form, I would populate the foreign key field with that CUA#.

[dgaletar]

I'm sorry if I'm asking too much again, but I'm not even 100% sure what a macro is. Could u explain in more detail, &/or give me an example?

[NeoPa]

Rabbit:
I would populate the foreign key field with that CUA#.

For this to work more seamlessly I would ensure that the foreign key control is set with the .DefaultValue, rather than the .Value, property set to the value passed. That way the buffer isn't dirtied and nothing goes awry if the operator decides to abort the process.

@Dgaletar.
I will try to give you some more detailed guidance later in the weekend but I'm just back from the pub now and I'm too tired :-(
One tip I'd leave you with. Try to break down the instructions into manageable chunks first before deciding it's all too complicated. You might surprise yourself.

[TheSmileyCoder]

There are a few issues to address.
Relationship
I presume 1 vehicle can have more then service performed. If you try to relate 1 primary key to another primary key, you can only have a 1-1 relation, not a 1-many relations.
I advice you to look at this article we have on our site :
Datebase normalization and table structures
It explains it very well.

The approach is to have the service table have its own primary KEY, and a foreign key that relates the multiple service record details to the single vehicle. Again, read the article, for more details.



Macro/VBA
Macros are basicly bits of pre-defined codes that can exposes a limited set of functions in access. Macros can be slightly easier to learn, but I find that it can sometimes take longer to achieve the goal you want, due to their limits. VBA (Visual Basic for Applications) will give you alot more power and control, but can take a bit longer to learn. A benefit of using VBA over macros is that experts on sites like this likely use 100% VBA and thus can help you with VBA but not with macros. 'Some' Macros however can be used in web databases whereas VBA cannot.

If you want to try the VBA approach, say so, and we can try to work something out.

[dgaletar]

OK TheSmileyCoder, I'm at home for the weekend so I can't try this right now. But I'm working on the first part of your response first. I just read the article you posted and I think that I get it. I think what I need to do is to have the CUA# as the foreign key in the "Service" table. That should relate the multiple service record details to the single vehicle like you suggested.

Does that sound right?

And, if it does, wouldn't that rectify my "button" problem?

[zmbd]

Before we go any further with this project it is very important that we know which version of Access you are working with! Things in V2003 been changed in V2007 and then again in V2010 - especially the macro language! There are things that you can now do (such as error trapping and use of the temporary variables in V2010) There are also some changes in the VBA and so forth.

[dgaletar]

My work establishment is currently using Access version 2010.

[TheSmileyCoder]

dgaletar
I think what I need to do is to have the CUA# as the foreign key in the "Service" table. That should relate the multiple service record details to the single vehicle like you suggested.

Does that sound right?

Spot on, that is correct.

Now what your macro is CURRENTLY doing is opening the form, and filtering to only display service records matching the vehicle. However your missing the part where you make sure that any service records added gets linked to the vehicle. I.e. we need to fill in the CUA# automatically, by setting the defaultvalue each time the form is opened.



The following is the VBA code approach
I always prefer to name my controls so its obvious what kind of control I am dealing with. Personally I would use tb_CUA for the CONTROL bound to the FIELD CUA# (And I would not use # or other special charecters in field or control names). In this cause both the Vehicle details form and the service details form would have a CUA#, and thus a tb_CUA. Notice that the 2 fields should be the same type of field, unless the CUA# is a Autonumber field in the Vehicle Details form. In that case CUA# in the service details form should be a Number(Long) type field.

With your form, make sure that AutoResize and AutoCenter and PopUp are all true/Yes, and that modal and AllowFilters is false/no. Make sure the tb_CUA in the Service Details form is Locked (To prevent user manually changing the number).

Private Sub btn_OpenServiceDetails_Click()
   'Open Form (Notice the frm prefix to let the coder know that its a form)
     'Notice that form is opened in ADD mode, and with a filter applied.
     DoCmd.OpenForm "frm_ServiceDetails", acNormal, , "[CUA#]=" & Me.tb_CUA, acFormAdd
 
   'Manipulate the form
     Dim f as Form
     Set f=Forms("frm_ServiceDetails") 
 
     'Set defaultvalue so that all records automatically get assigned the new id
       f.tb_CUA.DefaultValue = Me.tb_CUA
 
   'Set the form to be modal. This will prevent the user from clicking outside the form, until the form is closed again. 
      f.Modal = True
 
End Sub

Now you may ask, where should this code go? Well I presumed you have a button called btn_OpenServiceDetails. Select your button, name it as you see fit (Give it a meaningfullname), select the Event tab, and select the On Click property. You might allready see your macro there, if you are using the old button. Remove the macro, click the 3 dots (builder icon) on the right hand side. That should open VBA editor and show you:

Private Sub YOURBUTTONNAME_Click()
 
End Sub

and that is where you put the code.

After you have pasted the code, I strongly urge you to try and highlight/select various bits of the code as the OpenForm, and press F1 for additional information.


I hope you found this helpful, and not to confusing.I am sure there is a macro way of accomplising something similar, but I prefer the more detailed control I get using VBA.

[dgaletar]

OK, TheSmileyCoder, I feel like I am right on the brink!!! But, as usual, I am lost. I'm getting stuck at this sentence:

"Personally I would use tb_CUA for the CONTROL bound to the FIELD CUA# (And I would not use # or other special charecters in field or control names)."


Are you saying any of the following:

• that, in the "SERVICE : Table", instead of using CUA# I should use tb_CUA?
• or that, in ALL tables do that?
• or that I should name the command button tb_CUA?

I guess I am lost at "the CONTROL bound to the FIELD CUA#". What is the "control" and how is it "bound" to the Field CUA#?

Otherwise I think I'm there!

[TheSmileyCoder]

Good to hear, and thank you for providing such a precise response as to the parts you are confused about. It makes it much easier to avoid mis-understandings.


In tables, we have fields (sometimes called columns). A form has controls, which can be named. One (of many) properties of a control is the Controlsource.

Now if form frm_ServiceDetails is based on the table tbl_ServiceDetails then we can specify that the control(*) tb_CUA's controlsource is CUA#. You will often hear that the form is BOUND to the table tbl_ServiceDetails and that the control is BOUND to the FIELD CUA#.

When a control is bound it means that if we make edits while in form view, those edits carry through to the table.

So to deliver a more precise reply to your question:
Your table has a field named CUA#. I would recommend that the TEXTBOX control on both your forms, is named tb_CUA (tb shorthand for TextBox).

I would recommend the button gets called either btn_ServiceDetails (btn short for button) or cmd_ServiceDetails (cmd short for command). Note that the button NAME is different from the buttons CAPTION. The caption is what the users see on top of the button. The name is what you see when you look at your code. If you don't give the button a name your code will look like

Private Sub Command1_Click()
 
End Sub

(*) A control is usually one of the following: TextBox,Combobox, ListBox, Checkbox,Option Group, Image control, Subform Control

[dgaletar]

OK, I changed the names of the CUA# Controls for both forms (thanks to your expert explanation), saved them, and tried the button again and got this error:

Compile Error

Syntax Error

f.Modal = TrueEnd Sub

[dgaletar]

I GOT IT!!! IT WORKED!!! I forgot to change the name of the form!

Thank you SOOOOO much for sticking with me on this one!

[zmbd]

Sorry, I should have added the following link in my prior post too: Before Posting (VBA or SQL) Code

Of importance at this point is the first section "A"
By following the directions there, you will help to avoid some of the compile errors at run-time... such as the one you just encountered.

[TheSmileyCoder]

Thats lovely to hear. Its alot easier to work with people(like you) who can properly explain their question, and provide relevant details.

[dgaletar]

I couldn't have done it without your help TheSmileyCoder! Thank you.

Now, are you any good with "Aggregate Queries"? NeoPa is driving me nuts with his advanced way of instructing! I know he's trying, but he just doesn't get me.

Here's the thread: http://bytes.com/topic/access/answer...-our-inventory

If not, it's OK. I'll eventually figure it out. :)

[zmbd]

dgaletar:

You might look at cross tab query; however, you will need to start a new thread for that question. Both Rabbit and TheSmileyCoder are magicians with the SQL!

You might also take a look at my reply in the thread you note in post #18.

[dgaletar]

I found it! http://www.worldbestlearningcenter.c...e-function.htm worked perfectly! Thanks Bytes forum people. Hope to be back real soon!!!