Embeded Sub Forms

[Ray C]

I seem to have a couple of things going on here.
First, I have a Customer Form based on a Customers Table and I want to be
able to show Items of stock in a Sub Form based on a Query of the Stock
Table. I have embeded the sub Form on a Tab in the Customers Form but the
darned thing will not display when I give the Tab Focus. If I open the Sub
Form on it's own all of the Stock information is there but I want to display
that information from within the Customer Enquiry Form. Any Pointers?
Second, The query does strange things. My stock Table has a column called
SerialNo and this is the Criterion that I am searching on. The Serial numbers
contain Numeric and Alpha Numeric data so the Field is designated as a String
Field in the Table The first 13 Serial Numbers just Happen to be Alpha
Numeric i.e 12K345. The query will return any of those 13 when I enter the
correct information in the Criterion and the Form displays the correct
information. The 14th Serial No in the Table contains numeric digits only and
does not have any Alpha content at all i.e. 7446. If I ask the Query to look
up the 14th record the form is empty and the same for all the following
fields that contain Numeric only information. further up the Table are some
mre serial numbers with alpha content in them and the query finds those OK.
It seems that the whole thing falls apart if the Record does not have any
Apha content.
Whilst I have designated the SerialNo Field as a String Field is the Table
still storing anything that does not have any alpha content as a Number?
Thanks for any help
RayC

 

[Sprinks]

Ray,

Re: #1
Check the RecordSource property of the subform *itself* to see if you've
inadvertently changed it. Also, is there a relationship (i.e., a common
field) between the Customers table and Stock query?

If not, the LinkMasterFields and LinkChildFields properties of the subform
*control* should be blank, and the subform should show all records from the
underlying query.

If there IS a relationship, the LinkMasterFields and LinkChildFields
properties of the subform *control* should be set to the name of the field,
and the subform will show only those records associated with the master
customer record. If the customer doesn't "own" any "child" records, nothing
will display.

Re: #2:
No, something that looks like a number in a Text field is stored as text.
What's possible, though, particularly if you imported this data from some
other source is that the numeric values have a leading space. To remove the
spaces, perform an update query using the Trim function:

UPDATE SerialNos SET SerialNos.SerialNo = Trim([SerialNo]);

One last thing: It's part of the Newsgroup Etiquette guidelines to limit
your post to a single issue. It makes following the thread easier for others
to find and follow, but it also increases the chance that someone will answer
your question.

Hope that helps.
Sprinks

 

[Ray C]

Thanks for your response, please accept my apologies for my ignorance of the
Newsgroup Etiquette and appologies to all concerned.

Re#1 (and possibly #2)
Already done that and the subform does not have any Child Master
relationship and the properties are blank. The way I thought that this should
be able to work is that I have a "Find_SerialNo" text box for the user to
input the serial no he wants to find. The Query then uses the content of
"Find_SerialNo" in the Query SerialNo Criterion to find the record. The user
will have entered the number "7446" into the "Find_SerialNo" text box and I
have tried a number of different things in code to try and ensure that the
content of "Find_SerialNo" is indeed a String.,

'Find_SerialNo = Str(Find_SerialNo)
produces " 7446"
'Find_SerialNo = Right(Find_SerialNo, Len(Find_SerialNo) - 1)
puts this back to "7446"
Find_SerialNo = Format(Find_SerialNo)
also produces "7446"
The above are writen in the Find_SerialNo_After_Update() event.
The criterion that I am using in the Serial No Query Criterion is
"[forms]![frm_Enquiry_Cust]![Find_SerialNo]"

HOLD ON, I may have had a Eureka moment. If the User enters a number in the
"Find_SerialNo" Text box, would I be right in saying that the content is used
in the Criterion prior to the program executing the After Update Event.
Therefor this would result in the Query applying a Number as the Criterion
where the contents of the field was a String. This action may have completed
prior to my attempts at changing the contents to a String in the After Update
Event. Could I be anyware near the answer here or am I barking up completely
the wrong tree? If correct, would the answer be something like a "Requery"
after the above conversion of the number to a Strng. Is there any way for me
to thell the Texy Box to always treat any input as a String?

Regards RayC

Ray,

Re: #1
Check the RecordSource property of the subform *itself* to see if you've
inadvertently changed it. Also, is there a relationship (i.e., a common
field) between the Customers table and Stock query?

If not, the LinkMasterFields and LinkChildFields properties of the subform
*control* should be blank, and the subform should show all records from the
underlying query.

If there IS a relationship, the LinkMasterFields and LinkChildFields
properties of the subform *control* should be set to the name of the field,
and the subform will show only those records associated with the master
customer record. If the customer doesn't "own" any "child" records, nothing
will display.

Re: #2:
No, something that looks like a number in a Text field is stored as text.
What's possible, though, particularly if you imported this data from some
other source is that the numeric values have a leading space. To remove the
spaces, perform an update query using the Trim function:

UPDATE SerialNos SET SerialNos.SerialNo = Trim([SerialNo]);

One last thing: It's part of the Newsgroup Etiquette guidelines to limit
your post to a single issue. It makes following the thread easier for others
to find and follow, but it also increases the chance that someone will answer
your question.

Hope that helps.
Sprinks

I seem to have a couple of things going on here.
First, I have a Customer Form based on a Customers Table and I want to be
able to show Items of stock in a Sub Form based on a Query of the Stock
Table. I have embeded the sub Form on a Tab in the Customers Form but the
darned thing will not display when I give the Tab Focus. If I open the Sub
Form on it's own all of the Stock information is there but I want to display
that information from within the Customer Enquiry Form. Any Pointers?
Second, The query does strange things. My stock Table has a column called
SerialNo and this is the Criterion that I am searching on. The Serial numbers
contain Numeric and Alpha Numeric data so the Field is designated as a String
Field in the Table The first 13 Serial Numbers just Happen to be Alpha
Numeric i.e 12K345. The query will return any of those 13 when I enter the
correct information in the Criterion and the Form displays the correct
information. The 14th Serial No in the Table contains numeric digits only and
does not have any Alpha content at all i.e. 7446. If I ask the Query to look
up the 14th record the form is empty and the same for all the following
fields that contain Numeric only information. further up the Table are some
mre serial numbers with alpha content in them and the query finds those OK.
It seems that the whole thing falls apart if the Record does not have any
Apha content.
Whilst I have designated the SerialNo Field as a String Field is the Table
still storing anything that does not have any alpha content as a Number?
Thanks for any help
RayC