Find and display related records on a form (1 Viewer)

Maclain · Nov 21, 2017

Hi Everyone.

I have an ongoing issue where related records are not found by the users. To stop this, I'd like to try and display related records in a datasheet view sub form on our main form.

So, I have the following table:

MainTable

with fields:

PKfield (unique autonumber)
JobNumber (auto generated unique identifier in format yy/xxxxx)
Method (combo limit to list)
SerialNumber
Customer (combo limit to list)

The problem we have is we may create 2 records for a single item:

Lets say we have one item in for two methods, the item may have two serial numbers FX12345 and UT12345 with methods of Radiography and ultrasonics respectively.

The only thing that links the records up is the serial number excluding the first two characters.

in the query, I'm trying to use an * an the right function to limit the criteria for serial number from the table, so that I can then grab the serial number from the form and display only the related records by having a <> on the PKFLD.

How on earth do I get a like *(Right(Forms]![DayBook][SerialNumber],5)) to work as a critera?

Thanks for any help, and please ask if you require any other info.

isladogs · Nov 21, 2017

Use the Mid function

If the serial number always starts at the 3rd character, you would use:

Code:

Mid([Forms]![DayBook][COLOR="Red"][B].[/B][/COLOR][SerialNumber],3)

Don't forget the dot shown in RED

Maclain · Nov 21, 2017

Hi Ridders, thanks for your response

When I use a . instead of a ! for the filed name it doesn't show the field name as an option in the criteria.

Eitherway, using

Code:

Mid([Forms]![Job Register and Report Log]![Serial No],3)

or

Code:

Mid([Forms]![Job Register and Report Log].[Serial No],3)

shows no entries in the query.

Now I've disclosed my awful table and field naming

isladogs · Nov 21, 2017

Sorry, my brain wasn't in gear.

You need to refer to the underlying field name not the control name.
Not sure if its SerialNumber or Serial No as its changed from one post to the next.

So, something like this should work:

Code:

Mid([Serial No],3)

and yes, I would get rid of the spaces ....

EDIT:
OOPS - read your post again! If you are doing this from a query called via your form, you DO want the form control
Enter the full control name in the query criteria & run it. Does that give the value in the textbox?
If YES, then wrap it in the MID part and if done correctly it should work - CHECK your control name (SEE ABOVE)

Maclain · Nov 23, 2017

Hi Ridders,

My apologies, I was attempting to hide the poor naming

the query returns the correct record when I use:

Code:

[Forms]![Job Register and Report Log].[Serial No]

in the criteria, however, when I wrap this in the mid function it doesn't show any results:

Code:

Mid([Forms]![Job Register and Report Log].[Serial No],3)

For clarity I'm expecting this to display 2 records: FX12345 and UT12345

thanks for your help!

isladogs · Nov 23, 2017

My apologies, I was attempting to hide the poor naming

Yes I thought so!
OK now you've 'shamed' yourself, how about doing something about the naming ....

For real clarity, can you read post #4 & tell me exactly how you are using this

Also I suggest you upload a stripped down version of the database with some sample data

OR failing that export the relevant table/query to Excel & upload that

Maclain · Nov 23, 2017

Hi Ridders,

Honestly, the database is now 12 yeas old and is integrated into every part of our business, I would start again with it, but i simply dont have the time or expertise.

I intend to have a datasheet view of a form as a subform of the form [job register and report log] which pulls the information from the query.

Using thequery, i can obtain the required result using the crietia, why would the mid function fail?

isladogs · Nov 23, 2017

I'd need to see your database to answer that question.
Can you upload it or at least that part of it?

Maclain · Nov 23, 2017

Hang on, surely if the query is only taking the right side of the field, from character 3 then nothing will match the criteria unless there is a wildcard at the beginning?

Maclain · Nov 23, 2017

Code:

 Like "*" & Mid([Forms]![Job Register and Report Log]![Serial No],3)

works

isladogs · Nov 23, 2017

Glad you got it working
I just gave you a correct formula based on your original post

Maclain · Nov 24, 2017

Expanding on this a little,

I only want the criteria to apply if the Customer ID is fur001, else I want it to use the whole serial number field...

Code:

 IIf([Forms]![Job Register and Report Log]![Customer ID]="FUR001",Like "*" & Mid([Forms]![Job Register and Report Log]![Serial No],3),[Forms]![Job Register and Report Log]![Serial No])

However this just shows me everything...

isladogs · Nov 24, 2017

Yes it will ... for exactly the reasons you gave in the earlier post #9 when you had your light bulb moment!

Unfortunately, you only provided part of the picture before.
Looks like you need a rethink!

Maclain · Nov 27, 2017

Hi Ridders,

I don't follow, I'm passing the criteria a choice based on the value of Customer ID. Which part of the iif statement would cause it to show me all the records?

Thanks,

isladogs · Nov 27, 2017

When you first posted your request, you only gave part of the picture.
It would be easier to explain with example data

If you post the relevant table showing a few records with 'Customer ID' & 'Serial No' fields, I'll try & explain.

Make sure at least one record is for FUR001 and at least one for another customer

Maclain · Nov 27, 2017

Hi Ridders,

I've stripped out everything from the BE to just the customer and main table.

isladogs · Nov 27, 2017

Hi

To make my life easier, can I also have the form/subform & query items that you've referred to earlier in this thread

Maclain · Nov 27, 2017

I thought you might say that.

I've tried breaking it apart into the required forms query's tables, but everything just errors.

There's far too much data to try and strip out to make it worthwhile. Don't worry about it, it'll suffice until I figure another way.

Thanks very much for your help though, I appreciate it.

Maclain · Nov 27, 2017

it would appear that this:

Code:

Like IIf([Forms]![Job Register and Report Log]![Customer ID]="FUR001","*" & Mid([Forms]![Job Register and Report Log]![Serial No],3),[Forms]![Job Register and Report Log]![Serial No])

works, Apparently all I needed was to move the 'like' to before the iif statement.

isladogs · Nov 27, 2017

OK I'll believe you.
Glad you got it working ... hopefully

Find and display related records on a form (1 Viewer)

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Attachments

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Similar threads

Users who are viewing this thread