Enter a Code to retrieve all information from the selected row

[radeon]

Morning,

Excuse my grammar/English and I'm not an advanced user of Microsoft Access 2010, and not an expert on coding.

I've been working on a database for a company that does Non Destructive Testing, so my task is to simplify how the technicians process the information and make their jobs easier, I will try to be clear as possible in my question and hopefully someone will understand the concept I'm trying to do.



Firstly, I have assigned a Unique Identification to a code in this case - X8120 This code is assigned to a row of information.

What i want to do is enter the code X8120 into a text-box and once the code is enter, i want the database to retrieve all information that's assigned to that code from the row (which is 17 fields) in a table.



When the information is retrieve, i want those 17 fields to automatically populate into their own text-boxes, so there would be 17. This would all be on one form. So the technician only has to enter a code which displays the information relating to the testing the technicians have done.

What i have done so far is made the table into a form and added a select button command, so a technician would click on a button that opens a form and select the row of information they want and populates those 17 fields.

So my aim is to simply enter a code or an event and the database will retrieve the information instead of going through a form to select the information. So i am simplifying the process even more.

Hopefully I have clearly explained the situation if not, I'll be happy to assist further.

Thanks for reading and helping.

[orange]

Suggest you work through this tutorial, then use the techniques you learn to develop your own database.

You need a plan - a description of your testing activity - the things involved, how they relate to each other, the steps involved.....that's the "business description" and "business rules" - and you need to know some data base and design concepts. The tutorial shows you and uses the concepts, but you need to have a clear understanding of the "business".

Good luck.

[radeon]

I appreciate the feedback, the document is helpful, but it isn't the advice I'm seeking. I have developed the database for them, it's around 90% completion. Perhaps I haven't explained the situation I'm in clear enough? The company has 30 different types of service testing. Each service has it's own library of information (table) In each of the tables, there are 17 fields, Each row of information across those 17 fields has been given a unique code, as I stated above in this case - X8120

Now i have a form which deals with the interpretation results (what type of service and technical information used to carry out that service) and on that form are 17 text boxes for each one of those 17 fields.

What i am trying to achieve is, enter the code X8120 on that same form, in a textbox. That code has a row of information, Once i hit enter, i want to retrieve that row of information which is 17, and i want it to appear in those 17 text boxes on that form.

What i did before i asked for advice on here was created a button that opened a continuous form which list all the rows of information for a specific service and created a select command button so it will select that row of information and input it into those 17 textboxes. Once i selected the form closes.

So im trying to make it so you only have to enter the code on the form without having to open another form and manually select the row.

So how would i go about coding it or which process should i use?

[orange]

So, in overview you want to type a value into a textbox;
then the system will search and retrieve records with the entered value in some field(s);
then display the found records in 17 text boxes on a form.
In general terms:
You need a form with a text box.
In the afterUpdate event of the textbox, you need to invoke a search

You need to select records where somefield in your table has same value as your search argument
Then use that query as the recordsource of your form.

You may get some ideas from these links that have sample databases.

I put a keyword and fragment search example here.

There is a sample database within this thread that has several related comments.

[radeon]

Thank you for your advice Orange, it has guided myself in the right direction. Great response time, didn't have to wait very long. I will see what i can do.

[radeon]

My question follows on from this post. Thanks to orange guidance my issue was fixed. In the image, you'll see a red arrow - underneath the red arrow is a list box and above the red arrow are the 17 text-boxes. I can select/find a record from the list box and those 17 text-boxes will be auto-fill.

My question is that when i click print preview, none of those 17 text boxes with information in it, doesn't appear nor show on the reports. In each of those separate text boxes I've entered in this
=[SearchResults].[Column](1)
=[SearchResults].[Column](2)


SearchResults is the name of the listbox, so the database knows where to retrieve the information from.


Is anyone else to provide a explanation of why it isn't appearing on the reports? Or some guidance for a solution.

I have tried to link master field and child field but didn't work it.
Also I've tried to filter the information in the property sheet, but still no success.

Does this occur when the information isn't being saved or updated in the table or query when information is entered in those 17 text-boxes?

Orange - in response to your #4 post "In the after-update event of the text-box, you need to invoke a search

You need to select records where some field in your table has same value as your search argument
Then use that query as the record source of your form." I couldn't quite understand that statement, not sure if this is the issue as I don't have an after-update event on that form in the image above.

My relationships are good and no issues. (I would like to believe it is ^^)

Any help is appreciated and thanks.

[orange]

Not sure I'm following what you have done.
We were talking about a Form with a Textbox. Once you enter a value in the textbox, a search is invoked
to find records with that search value in some field(s). There are (based on your example) a number of records which meet your criteria. Those records are used to populate the 17 text boxes on the form. Are these text boxes bound to your underlying tables/query?
So far we are talking form - if you want to do a print preview, you'll be talking report. Have you created a Report?
The query that was used to SELECT records with the desired search term value, will now be used as the recordsource of your report.

You could use the report wizard to build a quick report to show the 17 fields (and others if needed).
Then, you would use the query mentioned above as the recordsource of the report. You would then open the report with print preview option.

As I recall, my fragmented search does similar action.

If I have misinterpreted or misunderstood your situation, please clarify.

Good luck.

[radeon]

In response to your question Orange "Those records are used to populate the 17 text boxes on the form. Are these text boxes bound to your underlying tables/query?" No, however the form that has the text-boxes on does have an underlying query which consists of the relevant tables for that form.
In each 17 text boxes has the "recordsource" set to
=[SearchResults].[Column](1) to =[SearchResults].[Column](17)

Those text boxes record-source aren't set to the fields that are in the query, if I did set the text boxes to the query then the purpose of entering a code to populate the fields becomes useless.

I have tried to create a report based on that form but none of those 17 text-boxes information appears on the report. The report is set to the query that I use on that form.

Yes the Fragmented Search does a similar action to what I'm trying to achieve but it's more hassle doing it that way, It defeats the purpose of simplifying the process. All the information that's on the form in the image above, is what I must have on a report.

Hopefully this makes sense.

If you would like more images or an uploaded zip folder of the DB, I'll be more than happy to assist.

[orange]

Yes upload a copy of the db in zip format.
If the 17 fields is just for display(READ ONLY), then the text boxes could be bound.
If you take the query with the search criteria, and use that as the starting point for a report and let the report wizard build that report, you would get a report with the data you want. Now the format won't be what you want, but it will have all of the fields. You can adjust the format/design of the report knowing the fields are available.

I think you are describing Controlsource here(not recordsource), but again, I'm unclear with the details
In each 17 text boxes has the "recordsource" set to
=[SearchResults].[Column](1) to =[SearchResults].[Column](17)

[radeon]

I'll try and explain in more finer details, i would want to upload a copy of the DB, but i don't wish for it to be available to the public, I've tried to send it as a private message Orange, couldn't see the attachment option. If there is another way, let me know.



As we speak Orange, 17 text boxes are currently just displaying and in the left image above you'll see =[SearchResults].[Column](1) in the text boxes. Now SearchResults is the NAME of the listbox, with a ROW SOURCE set to a query. COLUMN(1) tells the database which COLUMN to select that information from the list-box.

When i select the information i want from the listbox and those 17 fields are auto-populated, the records aren't updating in either query or tables.

Hopefully this has made more sense and the images have demonstrated my point

The middle image above demonstrates the query i have for that form and report basis. You may see and think that there are "duplicating fields" in those tables, but they aren't, each one has it's purpose.

The right image above demonstrates the report and shows that the 17 text boxes aren't appearing.

I've tried using report wizards, blank report, report design and report options, but none are successful.

If you have managed to understand the situation Orange, then through your expertise is there another way of resolving this issue instead of the way i attempted it?

[orange]

radeon,

Readers don't know enough about the tables and relationships and how they relate to your business rules.
I see fields in different tables that may be related. For example abbrev in table TBL_NDTreq and TBL_TestDescGam. When I create a query on those fields and tables I only get 1 record. I realize that it's a query based on "nothing but a guess", but as per PM I don't see any data being sent to the report for a large number of fields.
Perhaps you can tell readers in simple, plain English exactly what some of these tables represent and how the relationships fit the business rules.

Good luck.

[orange]

radeon,

Regarding you latest database - please check and verify that you have a control called SearchResult!!!!
I don't see it. Perhaps you removed it with your latest change???

[rpeare]

I see a couple of things in the screen shots that don't make sense to me and I don't see the attached database mentioned but here's what I do notice.

1. On your FORM FRMRECOMMENDATION, if you want to edit the data you can't use =searchresult.column(x), it will make the data uneditable. If you want to populate those fields with data and retain the ability to edit them in the ON CLICK event of the search box you need to have something like

FieldName = searchresults.column(x)

for each control you want to have data entry capability with.

I only mention this because this does not appear to be a lookup screen because you have a 'save' button.

2. If the issue is, on your report (RPTRECOMINTER), you are referencing what *should* be displayed in each of your 17 fields with the syntax =searchresult.column(x) that will fail as well, you would need to reference them by using

forms!frmrecommendation!fieldname

OR

forms!frmrecommendation!searchresult.column(x)

[rpeare]

Ok orange got me a copy of the database, if you are only using the subform FrmTestDescGam2 as a way to look up values for the main form, why not use a list box instead of a subform?

This is the code currently associated with the SUBFORM FrmTestDescGam2 ON CHANGE (which I have changed to ON EXIT) property of the SEARCHFOR field:

Dim vsearchString

vsearchString = SearchFor.Text
SrchText.Value = vsearchString
Me.SearchResults.Requery

If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
    Exit Sub
End If
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus
DoCmd.Requery
Me.SearchFor.SetFocus
If Not IsNull(Len(Me.SearchFor)) Then
    Me.SearchFor.SelStart = Len(Me.SearchFor)
End If

Can you tell me what you are trying to do with this code?

Be plain, step by step.

When I modify the RECORD SOURCE of the subform FRMTESTDESCGAM2 (Query QRY_TestDescGam) to:

SELECT TBL_TestDescGam.ID, TBL_TestDescGam.Abbrv, TBL_TestDescGam.Sch, TBL_TestDescGam.Size, TBL_TestDescGam.Source, TBL_TestDescGam.Technique, TBL_TestDescGam.Equip, TBL_TestDescGam.SizenActiveSize, TBL_TestDescGam.Exosure, TBL_TestDescGam.SFDnDFL, TBL_TestDescGam.OFD, TBL_TestDescGam.Film, TBL_TestDescGam.ScreenType, TBL_TestDescGam.Process, TBL_TestDescGam.Density, TBL_TestDescGam.IQILocation, TBL_TestDescGam.WireReq, TBL_TestDescGam.WireAchieved, TBL_TestDescGam.Material, TBL_TestDescGam.FocalnActiveSize, TBL_TestDescGam.FFDnDFL
FROM TBL_TestDescGam
WHERE (((TBL_TestDescGam.Abbrv) Like "*" & [Forms]![FrmRecommendation]![SearchFor] & "*"))
ORDER BY TBL_TestDescGam.ID;

And I remove the ON CHANGE property but add an ON EXIT property of the SEARCHFOR field of

Private Sub SearchFor_Exit(Cancel As Integer)
FrmTestDescGam2.Requery
RecFoundBox = DCount("*", "QRY_TestDescGam")
End Sub

The subform correctly updates with valid values in the ABBRV field and the record count field also updates correctly.

So my confusion is you seem to have a lot of extra code with no discernable end result

[orange]

rpeare,
It was me who suggested move from listbox to subform. I was doing some guessing and was trying to get data into the fields he was missing on his report. Perhaps, I guessed wrong--it happens more and more these days...
Thanks for your comments.