Mainform / subform / popup form

[Cat Winslow]

I have a main form frmCustomerSystems bases on a qry that combines all
customer data and the often-referenced part of the system data. It has 5
tabs, 2 contain much of that necessary system data. (I have a button that
allows input / view of the rest of the system info.) Two others have subforms
linked to show the Builder and the Installer of the system. Those all work
fine.

My problem is the tab that holds my DatesAndServices form. It's recordsource
is tblDatesAndServices, and has pk Service#, fk CustomerID, fk SystemID and
is currently linked (child/master) to the frmCustomerSystems by SystemID. (We
don't really care who the current customer is except to be able to find their
system easily; the system is our important item.) frmDatesAndServices will
pick up the SystemID from the current record on the main form, but not the
CustomerID. So something is needed there. The goal is that I can click a
button on frmDatesAndServices to open another form with all of the details
they need to fill in at each inspection, and I'd like to already see some
info that could automatically fill in from the main frmCustomerSystems
(custname, address, lot, etc). Am I asking too much? Or just asking the wrong
way?

So many thanks... (i hope this is a better explan. that long ago when i
started this...)

 

[Ken Snell \(MVP\)]

Do I understand that you want the subform to "link" using the SystemID and
CustomerID values? If yes, just add the CustomerID name to the
LinkChildFields and LinkMasterFields properties for the subform control.
Assuming that you already have SystemID there, this is the syntax:

SystemID;CustomerID

 

[Cat Winslow]

Ken,

Thanks SO much. Yes, this is the mini part. I now have the CustomerID and
the SystemID showing up on the DatesAndServices form. I have a button on this
form that is *supposed* to open the Inspection form. It will open the form,
but not populate the information that I want to repeat at the top. The top
section of the Information form has the CustID(from?), and various customer
address info, SystemID(from?) and the size, capacity, etc of the system, the
ServiceDate and ServiceType (from DatesAndServices), then oooodles of fields
for the user to enter, mostly preset to "S" (satifactory).

I have never had a problem with doing this in a form before, so I guess the
setup is a bit different here with all these forms needing each other's data..

 

[Cat Winslow]

I am turning blue with this, but I'm getting there, thanks for your
patience... I just also changed the SQL statement to Left joins, and I now
can get the info to populate the top of the form correctly. But (you knew it
was coming) none of my fields set to "S" or Null on this form show their
default values until I type one of them in. Then they all populate, but they
shouldn't. It seems they are repeating another record, but not the one just
above it if I look in the Inspections table. So maybe this is a step ahead,
or maybe back...

 

[Ken Snell \(MVP\)]

I know that I am not fully understanding your setup, but it sounds as if
your form is not actually beginning a new record until you begin to type
into the new record in the Inspections form? I assume that the "S" values,
etc. that you mention are default values assigned to either the fields
themselves or to controls bound to the fields? How are you opening the
Inspections form -- do you use DoCmd.OpenForm action? If yes, are you using
the acFormAdd constant for the fifth argument in the OpenForm method?

 

[Cat Winslow]

Ken,

Thanks for your response, and your help!

"The form begins a new record" is true usually (although we will need to go
back sometimes and look up a form already input). In frmDatesAndServices,
SericeNumber is autonum (might be part of my problem in the other form?) and
then I type in the Problem, ReportDate, and InspectionType. Then I want to
click the button and open the form with all of this data and the Customer and
System data filling in also. The S values are defaults bound to most field
controls.

I have worked in Access for a while without having to do much vb, other than
what was fairly intuitive, so I just used the button wizard to open the form.
There is probably a better way that would just find the values that are in
the currently open records, but I am really new at the code part.

 

[Ken Snell \(MVP\)]

I am not 100% certain, as I do not have full knowledge of your setup, but
the behavior you see for the default values likely will not be overcome
unless you actually write a value into one of the fields in the form's new
record. This can be done via programming if you want (e.g., write a default
value into a control in the form's load event, like this:

Private Form_Load()
Me!NameOfAControl.Value = Me!NameOfAControl.DefaultValue
End Sub

and that will definitely start the new record.)

 

[Cat Winslow]

Good idea, but no go. I tried
Me!Accessible.Value = Me!Accessible.DefaultValue
Me!Accessible.Value = Me!Accessible.S
Me!Accessible.Value = Me!Accessible."S"
and got errors on all. (I put the code in the OnLoad event of the form.)
Would it be too difficult or bad programming to write something that copies
the current record field valuesthat I want from my main frmCustomersSystems
and my subfDAtesAndServices to a new record, and put _that_ in the OnLoad
propert of the form?

I am determined for this to work, the _right_ way (somehow)!

Cat

 

[Ken Snell \(MVP\)]

What errors did you get with those code steps?

 

[Cat Winslow]

Ken,

Amazing how looking at one thing can lead us to another and BINGO! I am
getting closer I changed my underlying query to a left join and I now get
the fields filling in as I needed from the main forms.

Here's the last part, very wierd, and I cannot figure it out. All of the
"S" values now come up as defaults correctly on my Inspection form, but I
have about 10 fields that are Yes/No's that are defaulting to "No". I have
them set at the table level AND the form control to default to Null, but they
just won't. Any ideas on that one?

Thanks!! Cat

 

[Ken Snell \(MVP\)]

A Yes/No field is boolean, and cannot hold a Null value -- only Yes
(actually -1 for True) or No (actually 0 for False). So a "null" value is
treated as false, which is what you're seeing.