Form/SubForm - REPOST - ANSWER STILL ILLUDES ME

[Guest]

Michael Miller provided some information on this topic. I would like to acknowledge his response and thank him for his time. Though the forms are working correctly the problem I have is that there are default values that are not being entered because the form is never activated on the tab control. These defaults are crucial to other data that is being collected. Please advise and thank you

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.

Thanks in advance. Totally lost.

 

[Graham Mandeno]

Hi John

This is the way 1:1 relationships are supposed to work. You use them when
the information in the second subordinate table is optional.

First, I suggest you think about whether the fields in your subordinate
table actually belong in the main table. If so, then change the design.

If you conclude that your current design *is* appropriate, then you can use
the AfterUpdate event of your main form to check that a related record
exists and, if not, then execute a simple SQL statement to add one.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Michael Miller provided some information on this topic. I would like to

acknowledge his response and thank him for his time. Though the forms are
working correctly the problem I have is that there are default values that
are not being entered because the form is never activated on the tab
control. These defaults are crucial to other data that is being collected.
Please advise and thank you.

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.

 

[Guest]

Thanks for the response. Based on the way the database is setup and the information being housed the ProspectInformation table is appropriate.

I've concluded as you have as well that the after update in the main form would be the most appropriate. My problem though is I have no idea how to write the sql statement that would make the validation. I'm not all that swift when it comes to actually writing sql statement.

Could you possibly help me out with this. It would be much appreciated.

The two field that I need to ensure are not null is the PrspInfoHSAttend and the PrspInfoClassification.

Much appreciate the help.

 

[Graham Mandeno]

Hi John

Well, it's easy enough, but what values do you want to put in there? Do you
have a default value set for each of the fields in your table design? If
so, all you need do is create the record with the required primary key:

CurrentDb.Execute "Insert into tbl_ProspectInformation (PrspInfoID)
values (" _
& Me.PrspDemoID & ");"

If you need to specify certain values for the other fields, then add them to
the SQL statement.

Note that if PrspInfoID is the primary key, this will fail if the related
record already exists (or if any other problem occurs). However, it will
not generate any error message, unless you add a second argument,
dbFailOnError. If you do this, you will have to check first (say, with
DLookup or DCount) to see if the record exists.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the response. Based on the way the database is setup and the

information being housed the ProspectInformation table is appropriate.

I've concluded as you have as well that the after update in the main form

would be the most appropriate. My problem though is I have no idea how to
write the sql statement that would make the validation. I'm not all that
swift when it comes to actually writing sql statement.

Could you possibly help me out with this. It would be much appreciated.

The two field that I need to ensure are not null is the PrspInfoHSAttend

and the PrspInfoClassification.

 

[Guest]

That's exactly what I'm looking for Graham. Problem though. When you go to do a new record the defaults appear nicely in the sub form. But if you save the record I get a debug error highlighted this parts of the sql statement.

values ("_ & Me.PrspDemoID & ");

What exactly would I be missing here. Here are the two fields in the sub form that have default values that are required regardless if data entry is down or not. PrspInfoHSAttend = Default Value is 6666666 desc Unknown. PrspInfoClassification = Default Value is "FR" desc Freshman

Thanks once again. Not sure if I need to fill something in on your syntex or not. Other then this problem this is exactly what I'm looking for

 

[Graham Mandeno]

John

This wrapped badly in the newsreader. The "_" is just a VBA line
continuation character, and is not part of the SQL statement. Here it is
again:

CurrentDb.Execute _
"Insert into tbl_ProspectInformation (PrspInfoID) " _
& "values (" & Me.PrspDemoID & ");"

This assumes your primary key field is numeric. If it is text, then enclose
the value in quotes:

... & "values ( '" & Me.PrspDemoID & "' );"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi John

Well, it's easy enough, but what values do you want to put in there? Do you
have a default value set for each of the fields in your table design? If
so, all you need do is create the record with the required primary key:

CurrentDb.Execute "Insert into tbl_ProspectInformation (PrspInfoID)
values (" _
& Me.PrspDemoID & ");"

If you need to specify certain values for the other fields, then add them to
the SQL statement.

Note that if PrspInfoID is the primary key, this will fail if the related
record already exists (or if any other problem occurs). However, it will
not generate any error message, unless you add a second argument,
dbFailOnError. If you do this, you will have to check first (say, with
DLookup or DCount) to see if the record exists.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the response. Based on the way the database is setup and the

information being housed the ProspectInformation table is appropriate.

I've concluded as you have as well that the after update in the main

form
would be the most appropriate. My problem though is I have no idea how to
write the sql statement that would make the validation. I'm not all that
swift when it comes to actually writing sql statement.

Could you possibly help me out with this. It would be much appreciated.

The two field that I need to ensure are not null is the PrspInfoHSAttend

and the PrspInfoClassification.

Much appreciate the help.