SQL Statement - Form/SubForm

[Guest]

Two tables. tbl_ProspectDemoInformation and tbl_ProspectInformation

Form ProspectInformation pulling data from tbl_ProspectDemoInformation
SubForm subfrm_ProspectInformation pulling data from tbl_ProspectInformation

Users are entering data in the ProspectInformation form but not in the subfrm_ProspectInformation form

Problem having is if no record is entered in the sub form no record gets generated in the tbl_ProspectInformation which is relates to tbl_ProspectDemoInformation with a 1-1 relationship. Master and child links are setup correctly. This only occurs when they do not enter something in the sub form

Is there a way to force the system to check HSAttended field and if it is null take the cursor to that field in the tab subform. Currently their clicking the save command without entering any record in the sub form.

Everything is correct table tbl_ProspectInformation is required etc. In the forms discussion someone mentioned making an sql statement in the after update of the main form. I agree with that conclusion. Problem is I'm not very swift at sql statements and have no idea how to write one

Can someone please please help out. Totally frustrated with this one

Here's the info that I hope will will. PrsInfoHSAttend can not me null and Classification = FR for freshman

I really would appreciate help on writing this sql statement.

 

[tina]

when you're "not very swift at sql statements" (which describes me, too),
you can "cheat" by first building a query using the QBE grid in query design
view. when the query is pulling the record(s) you need, click View, SQL from
the menu bar in design view, and copy the SQL statement to the clipboard.
then paste it into your code where you need it.

hth

Two tables. tbl_ProspectDemoInformation and tbl_ProspectInformation.

Form ProspectInformation pulling data from tbl_ProspectDemoInformation.
SubForm subfrm_ProspectInformation pulling data from tbl_ProspectInformation.

Users are entering data in the ProspectInformation form but not in the

subfrm_ProspectInformation form.

Problem having is if no record is entered in the sub form no record gets

generated in the tbl_ProspectInformation which is relates to
tbl_ProspectDemoInformation with a 1-1 relationship. Master and child links
are setup correctly. This only occurs when they do not enter something in
the sub form.

Is there a way to force the system to check HSAttended field and if it is

null take the cursor to that field in the tab subform. Currently their
clicking the save command without entering any record in the sub form.

Everything is correct table tbl_ProspectInformation is required etc. In

the forms discussion someone mentioned making an sql statement in the after
update of the main form. I agree with that conclusion. Problem is I'm not
very swift at sql statements and have no idea how to write one.

Can someone please please help out. Totally frustrated with this one.

Here's the info that I hope will will. PrsInfoHSAttend can not me null

and Classification = FR for freshman.

 

[Brian Kastel]

This works both ways, too. I place a breakpoint in my code after the sql
string is created, then open a new query in design mode without attaching
any tables and go into SQL view to paste my string. When I then switch to
design mode, I can spot unexpected (read: programmer errors) before they
create unexpected (read: hard to find) bugs.

--
Brian Kastel


--Original Message----------------

when you're "not very swift at sql statements" (which describes me, too),
you can "cheat" by first building a query using the QBE grid in query design
view. when the query is pulling the record(s) you need, click View, SQL from
the menu bar in design view, and copy the SQL statement to the clipboard.
then paste it into your code where you need it.

hth

Two tables. tbl_ProspectDemoInformation and tbl_ProspectInformation.

Form ProspectInformation pulling data from tbl_ProspectDemoInformation.
SubForm subfrm_ProspectInformation pulling data from tbl_ProspectInformation.

Users are entering data in the ProspectInformation form but not in the

subfrm_ProspectInformation form.

Problem having is if no record is entered in the sub form no record gets

generated in the tbl_ProspectInformation which is relates to
tbl_ProspectDemoInformation with a 1-1 relationship. Master and child links
are setup correctly. This only occurs when they do not enter something in
the sub form.

Is there a way to force the system to check HSAttended field and if it is

null take the cursor to that field in the tab subform. Currently their
clicking the save command without entering any record in the sub form.

Everything is correct table tbl_ProspectInformation is required etc. In

the forms discussion someone mentioned making an sql statement in the after
update of the main form. I agree with that conclusion. Problem is I'm not
very swift at sql statements and have no idea how to write one.

Can someone please please help out. Totally frustrated with this one.

Here's the info that I hope will will. PrsInfoHSAttend can not me null

and Classification = FR for freshman.

 

[Albert D. Kallal]

Just to be clear here, most repots and most queries SHOULD NOT care if those
child records exist, or don't.

And, further ..if reports DO CARE, then likely you have setup your
relationships wrong.

In my applications, about 95% of the relations I setup in the ER window are
left joins. A left join simply means that if I have a customer, and a bunch
of invoices for that customer, then the customers will STILL appear in the
report if they have NO invoices.

So, it would seem to me, that your design should not care one bit that a
child record does, or does not exist?

Why do you need that child record?

If you take a look at the following ER diagram, you can see that the
majority of the tables DO NOT requite child records.

http://www.attcanada.net/~kallal.msn/Articles/PickSql/Appendex2.html

However, if you look at tblBooking (a booking), you can see that my designs
assume that there MUST be a tblBgroup (in other words, I don't allow a
booking to occur UNLESS a group of people is present (tblBgroup). This makes
sense! You can see that the join line DOES NOT have a arrow head (the line
from tblBooking to tblBgroup has NO arrow). However, if you look at
tblBgroup, and the join line to tblPayments, you can see an arrow head. This
obviously is a left join, since people CAN be booked..but not yet have made
any payments. If you look at the MOST tables in the ER diagram, you can see
most of the time my designs, code, reports etc DO NOT assume that child
records need be present. (hence you see arrow heads in MOST of the joins).

The other interesting thing here is that by setting up your relations
correctly, a rather large amount of information and assumptions about the
relationships that the original developers had can be made here.

My only point here is that by using correct relations between the
tables..then things should function just fine WITHOUT child records (and,
that ER diagram is proof of this concept).

Have you tried using a left join....and thus not need the child record?

 

[Guest]

Thanks you all for your comments. I'm not quite sure how to do what Tina and Brian are saying. I didn't originally develop this data but I have the major task of fixed most of it. Matter of fact the relationships didn't even exist correctly

Albert I took a look at your diagram and it all makes sense. I think the point that everyone is missing though is that there are default fault values that need to be entered in table tbl_ProspectInformation regardless if the user enters any data in the tab control subform_ProspectInformation. These defaults are not being accepted because the form is never activated out of the main form if the user enters data in the main and then just clicks save

I'm desperate at this point. I just a simple sql statement code. Can someone please help. I don't know what to do here. This is the very last thing I really needed done but I can't seem to find a solution. Everyone's been really helpless but the answer that I need continues to escape me

Thanks in advance.

 

[Elwin]

The whole point of a 1 to 1 relationship is that one side
is optional, otherwise you'd just keep all fields in the
same table. With that said, I know you don't want to
tinker too much, so here's what you asked for in your
original post. It checks the value of a field
(HSAttended?) on your subform and will hopefully take the
cursor there if it's null. Good luck John.

Dim ctl As Control
Set ctl = Me!SubformControlName.Form!HSAttendedControlName
If IsNull(ctl) Then
MsgBox "Entry Required",vbInformation,"Alert"
Me!TabControlPageName.SetFocus 'line may not be needed
ctl.SetFocus
Exit Sub
End If

-----Original Message-----
Thanks you all for your comments. I'm not quite sure how

to do what Tina and Brian are saying. I didn't originally
develop this data but I have the major task of fixed most
of it. Matter of fact the relationships didn't even exist
correctly.

Albert I took a look at your diagram and it all makes

sense. I think the point that everyone is missing though
is that there are default fault values that need to be
entered in table tbl_ProspectInformation regardless if the
user enters any data in the tab control
subform_ProspectInformation. These defaults are not being
accepted because the form is never activated out of the
main form if the user enters data in the main and then
just clicks save.

I'm desperate at this point. I just a simple sql

statement code. Can someone please help. I don't know
what to do here. This is the very last thing I really
needed done but I can't seem to find a solution.
Everyone's been really helpless but the answer that I need
continues to escape me.

 

[tina]

that will take the cursor to the subform control, but will not force data
entry. without further coding, presumably the user can simply close the form
at that point - the main form's record is already saved. depending on how
the form is set up, the user may be able to click back into a control in the
main form and press the Page Down or Page Up key to go to another record -
still without making an entry in the subform.
to add a record with default values to tbl_ProspectInformation, it would
read something like this, to run in a query:

INSERT INTO tbl_ProspectInformation ( PrimaryKeyField, Classification,
PrsInfoHSAttend )
SELECT Forms!ProspectInformation!PrimaryKeyField, "FR", "something";

and something like this, to run in VBA:

DoCmd.RunSQL "INSERT INTO " _
& "tbl_ProspectInformation ( " _
& "PrimaryKeyField, Classification, " _
& "PrsInfoHSAttend ) SELECT " _
& Forms!ProspectInformation!PrimaryKeyField _
& ", 'FR', 'something'", False

you didn't mention the field names of the two tables' linking fields, so
you'll have to substitute those for the PrimaryKeyField instances above.
also, your original post said only that the PrsInfoHSAttend can't be null -
but you didn't say what default value needs to be used, or if a
"record-specific" value should be used, and where to get it. so i had no
idea what to put in the statement in place of the "something".

hth

 

[Elwin]

All Tina's points are valid, but I didn't want to burden
you with complications if just a little patch was all you
wanted. Hopefully you're able to successfully implement
her SQL.

If not... when you click the 'debug' button, is the 'Set
ctl' line highlighted? If so, then change PrsInfoHSAttend
to the NAME of the CONTROL on the subform (I think you've
used the field name from the table). You can find the
name by clicking on the property button in the menu bar
when the control has focus in design view.

Otherwise there is a more fundamental flaw in your form
design, which wouldn't be a complete surprise since the
Main form won't save without a related entry.

-----Original Message-----
Thanks Elwin so much for your help.

I took the command you provide and made the necessary

modification. Here is your command with the modifications.

Private Sub Form_AfterUpdate()

Dim ctl As Control
Set ctl = Me!subfrm_ProspectInformation.Form! PrsInfoHSAttend
If IsNull(ctl) Then
MsgBox "Entry Required", vbInformation, "Alert"
Me!tab_Academic.SetFocus 'line may not be needed
ctl.SetFocus
Exit Sub
End If

End Sub

Now I receive error code runtime error 2465. Microsoft

Access can't find the field PrspInfoHSAttend referred to
in your expression.

Spelling and everything is correct. Is there something

I'm doing wrong. I can feel that I'm so close that I can
touch it. lol Maybe there a light at the end of this
tunnel on this one.

Please advise and thanks for hanging in there with me

folks. I really can't believe no one else is having this
problem. I've look all over the group for a similiar
issue but I haven't found anything.

 

[Albert D. Kallal]

Your particular module doesn't show a way to force these defaults into the
table out of the sub form in the control tab if the user clicks saves on the
main form.

Well, actually, it kind of does..since if you setup your relations..this
takes NO code!

I explain a no code solution at the end of this...

Note that if you have the child/sub form setup correctly, then ms-access
will automatically place in the key field for you. So, the only thing you
need is to shove in the defaults....

Just use the following code in your main forms "before update" event

Set ctl = Me!SubformControlName.Form!HSAttendedControlName

if isnull(ctl) = true then
ctl.Value = "your default value"
end if

You don't need to use any sql here. Just repeat the above syntax for
the 2nd field you want to set...

And, "your default value" can be any data value available on the main form

ctr.Value = me!Classification...or whatever..

The above syntax is:

me!YourSubFromContorlName.Form!YourFieldName

So, no, you don't need a bunch of sql, or whatever. I could 3 lines of
code...

Now, as mentioned..you can also use a 100% code free solution if you wish.

All you have to do is change that sub-form on the tab to a regular bunch of
fields. Since this is a one to one relationship, then you do NOT need to use
a sub-form. You should use a just some regular fields.

So, to do this, you just built a nice query in the query builder. You place
all the fields from the main tables (you can click on the "*") to use all
fields.

Now drop in the child table. Draw a join line from the main table to the
child table. If your relations are setup, then ms-access will draw the join
line for you.

Now, go to your form, and delete the sub-form with those fields. Now, change
the data source of the form to the above query. If you look at the list of
fields in the view->field list, You will see ALL of the fields from both
tables.

It is now a simply matter to place those child fields on the screen, and set
a default for those two fields. This means that NO code at a all is
need to automatically create this related record. You do not need to run a
bunch of sql, or code or even write ONE line of code to check if the child
table record exists exits or not. If you built the query and use that for
the form..then the whole process works without any code, or even any
checking....

 

[Guest]

Thank you everyone for all your response. Your all absolutely correct

Albert I thought of that idea you mentioned and it appears I will be going in that fashion to avoid all the coding necessary. I new about that process but was hesitate and still am due to the following. All the forms are running directly from their associated tables. What impact if any would occur if I did the following

1. Run the main from from the query. I can do associate the two tables in the query which will automatically assign the relationship as Albert had mentioned

2. I can then still run the sub form as is and change the respective two fields to run from the query as well. I would then place those two fields on the main form as copied and hide the fields. The end result would be that no matter what you do if you try to save a record etc. The system would not allow the save to take place due to the two fields that are located on the sub form and also on the main form as hidden would require entry

I think I pretty much said what Albert did in a round about way.

As stated before though is it a bad idea to run one form off a query and others off the main table. I usually run all of them off the main table. Can someone explain me the difference as to benefits and negative for running them from their associated tables or from generated queries. I think I fell asleep some where on that one

Thanks so much for all your help. Everyone. You've all been extremely helpfull.

 

[tina]

duh! <smacks herself on the forehead>
i forgot the ease of a one-to-one relationship - where were you when i was
wiping sweat off my brow, Albert? <g>