Enter a Code to retrieve all information from the selected row

[rpeare]

We were thinking along the same lines then I couldn't understand why you would set those controls to an uneditable formula.

[orange]

The =[Searchresults... ] for the additional fields was in the original set up by the OP. I didn't change any of it.

[radeon]

Hey Orange & rpeare, thanks for helping & contributing your suggestions so far and I appreciate the time you spent in to trying and resolve this.

This code was associated with the listbox (name of the listbox is SearchResults, which I’ve currently placed in form1, while I try out the subform approach.) The code does work when you enter a Abbrv code but the information doesn’t to appear on the RptInterRecomm. (This was when the listbox was on FrmRecommendation.)

I have a SearchFor, SrchText and a RecFoundBox textbox which was assigned to that code for that listbox, I left the code in place until the issue was fixed, and so for now it’s sitting there. Far as I’m aware that code is not attached to FrmTestDescGam2, the code was attached to the FrmRecommendation I wanted to try and use that code and relate it to the FrmTestDescGam2 but at the moment I’m not.

Now, it seems like you removed this code from the form/subform entirely? Is that correct? I don’t need this no more?

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


I added this code to the ON EXIT property of the SEARCHFOR as you mentioned and removed on ON CHANGE property of the SEARCHFOR

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


Now when I enter a Abbrv code into the SEARCHFOR, I get an error saying I can’t assign a value to this object and when I debug it points to the RecFoundBox = DCount("*", "QRY_TestDescGam") did I miss something?

Other thing you spoke of Rpeare is the uneditable formula that was in those 18 textboxes, I replaced it this searchresults.column(x) to this =[FrmTestDescGam2].[Form]![Abbrv] on the FrmRecommendation.


On the RptRecomInter I also added this formula in those 18 textboxes =[Forms]![FrmRecommendation]![FrmTestDescGam2].[Form]![Abbrv]

Is this correct or incorrect formula?

Thanks

[rpeare]

orange, my fault on the listbox/subform question I skimmed a bit and missed that.

radeon, what is the GOAL of this form, do you intend to do data entry on the items you currently have in your subform or is this form strictly for changing/editing the MAIN record and printing a report? If your goal is to do data entry on the items in the subform you either need the subform (as orange correctly identified) or you need to have unbound controls and some code to update the information.

If, on the other hand, your goal is to just use this form as a lookup you don't need the subform and the list box will work just fine.

After re-reading a bit I think orange is trying to find out what your INTENT with this form is.

1. Data entry on all elements visible on the form
2. Data entry for ONLY the MAIN elements on the form with a lookup of associated items strictly for printing purposes.

orange can correct me if I'm screwing up this interpretation

[radeon]

This is for the readers;

Readers will see the same field titles in certain tables; I can assure you they aren’t, each one has its purpose.
The database is split into 3 stages; I’ll put it this way as an example.
The technician will begin by going to Log Request, select the details then move on to the next section where there are 3 tabbed pages – “Project Log”, “Requested Service” and “Radiography”.

“Project Log” entails information about the client

“Requested Service” entails which type of service testing the client wants carried out and the “Code” combobox entails information relating to the service testing, which is the specification standards which must meet the acceptance specification standards.

In Non-Destructive Testing, a client would for example send our technician 8 pipes (these are pipes that will be tested to ensure they are safe before going offshore)
In the “Radiography tabbed”, the technician would enter 8 rows of information relating to each of those 8 pipes, and when printing a Sleeve or Label, 8 of those rows would be taken into the Sleeve & label and will also print a report.

On the “FrmMainMenu” you have “Report” this entails information about the service that was carried out. This is what the client gets when the service is completed and drafted into a final report.

On “FrmMainMenu”, you have Interpretation, this entails information that used to carry out the service and it’s technical information (the listbox), also it produces an interpretation result – meaning was the testing acceptable to the required standards or was it rejected due to faults and defects?

Now, once this listbox issue has been resolve, I can use that process and place it onto the “Project Log” and show it on RptLogsheet as well as the RptRecommInter. (My boss wants it on both of those reports)

The goal of that subform is to act as a library that can select information from it and enter information in it, over a period of time. When the information is selected and populated those 18 textboxes, 9 of them won't have any information in - that's because the technician NEEDS to enter the data manually for those text-boxes, so it has to be updated, saved and in sync.

List below represents 9 out of those 18 Text-boxes, and they have information stored in its columns because it wouldn't ever change nor be edited but it may have additional information entered.

Sch
Source
Equip
SizenActiveSize
SFD/DFL
OFD
ScreenType
Process
IQILocation

The list below represents the other 9 out of 18 text-boxes and these does not have any information stored, this is because the technician will enter that data manually and/or select it from a drop-down box because the information will always be different.

Exposure
Film
Density
WireReq
WireAchieved
Material
Tech
FocalActiveSize
FFD / DFL

[rpeare]

Ok so the goal is to do data entry with this form. And in digging through your tables you have some serious flaws here.

The two tables in question for this form are tbl_TESTDESCGAM and tbl_NDTReq

tbl_TestDescGam has an autonumber field to provide a unique identifier, but you are storing a text based description field in your table tbl_NDTReq. Let me stress again this is a serious problem, this means that if you change (even by accident) the ABBRV field in your table tbl_TestDescGam you are going to orphan ALL of the data in tbl_NDTReq.

This goes for ALL of your tables as far as I can tell (I didn't look at every data point, just scanned through them). On top of that some of your tables have text based unique identifiers, some have autonumber unique identifiers you should be consistent unless there's a really good reason not to be.

There are a huge number of inconsistencies in your table design, and now I understand orange's confusion, for instance in your table tbl_TESTTYPE You have the fields IDTestType, Description, ReportTitle you have set DESCRIPTION to be the primary key which is a very bad practice, you have primary keys for a reason, this table should have an autonumber primary key, then you can assign the IDTestType to be the testing code (if you use the code for data entry), this would allow you to change your code if necessary and NOT destroy your existing data.

In short you need to overhaul your data.
1. All your tables should have an autonumber pk (I'm suggesting this only because it is the easiest way to enforce a PK for most users and frankly an indexed number is a lot faster in general use than a text based one)
2. All your tables, when storing a value from another table should store the FOREIGN KEY (the external table's PK) and NOT the text value of one of the fields within that table
3. If the goal of your PK in some of these tables (like your clients table, where the PK is the CLIENT field) is to avoid entering duplicates there are better ways to do it than what you're doing

Now specifically related to the original question:

This is where orange was talking about WHAT IS YOUR BUSINESS MODEL? Do not repeat what you've typed because it is not clear.

In other words when I look at this I assume for each record in your tbl_NDTReq that there would be one, and ONLY one record in your table tbl_TestDescGam (a one to one relationship). This means you could NEVER re-use any of the values in the ABBRV field. And you would want to store the ID field from the table tbl_TestDescGam in the table tbl_NDTReq *NOT* the ABBRV field.

If this is not your intended design you have the wrong structure.

If your goal is that for each record in tbl_NDTReq there would be multiple 'tests' performed, and you want to choose which tests would be performed from the list of items in tbl_TestDescGam then you would need a junction table connecting the records in tbl_NDTReq to the records in tbl_TestDescGam

In your most recent post:

The database is split into 3 stages; I’ll put it this way as an example.
The technician will begin by going to Log Request, select the details then move on to the next section where there are 3 tabbed pages – “Project Log”, “Requested Service” and “Radiography”.

Your original post was about frmRecommendation

There is no form that I can readily identify as 'log request' but I assume you mean frmLogRef and the ensuing form frmLogReqBlue, which has the table source tblLogReq

How does the record get from tbl_LogReq to the table tbl_NDTReq? Your 'save' button on the request form does nothing (no code attached) and I am not going to go hunting through your forms. If that is part of your data entry on the form 'frmRecommendation' there is no place to enter it, your 'jobno' field on your form is set to the table tbl_LogReq JobNo field which means you can't update it because JobNo on tbl_LogReq is an autonumber field.

I'm going to stop there because there are huge number of problems with this database, perhaps once clean up your data and clarify what you want (i.e. multiple records in tbl_TestDescGam for each record in tbl_NDTReq) we can have another look at it.