How do I limit the data in a split form based on information in the primary form?

[Umpire]

I have extremely limited experience so please be gentle.

I have a maintenance database with over 6k records. I have a form for entering the individual tests. I have made this form a split form.
When I enter the Serial Number in the Main Form (left side) I want the Split Form(Right side) to only show those records that match that serial number.

I have attached a sample database showing the type of form I want to use.

If I enter a new test for Serial Number A568 for example, I want the 2 previous entries to be the only ones showing on the split form.

I expect it will involve an Event Procedure (on exit?) but I don't know how to filter the data properly.

Thanks

 

[pbaldy]

I don't think you want a split form (a specific type of form), which does the opposite of what you're asking. It brings the main form to whatever record is selected in the split section. You could use a main form with a subform, and filter the subform based on the record/entry in the main form. That code would look like:

Me.SubformControlName.Form.Filter = "[Serial Number] = '" & Me.[Serial Number] & "'"

FYI, spaces in object names are not worth the bother in the long run. It forces you to bracket the name.

 

[Umpire]

Thank you for the response. Your comment about spaces in names is duly noted.

It does look like a subform is the correct choice (Time to learn something new)

My concern is that in looking at the help files, the data in the main form and the sub need to be in 2 seperate tables. That is not the case with my database. All of my needed data is in the same table. Is this going to be a problem?

 

[pbaldy]

It is common that the data in a form/subform is from different tables, but not required. You would need to watch out for write conflicts, since you'd have 2 places viewing the same record. If all you're doing in the main form is entering a record to view in the subform, the main form doesn't have to be bound to a table at all. It could just have a textbox or combo where you enter the desired serial number. I don't really know the overall picture of what you're trying to accomplish.

 

[Umpire]

Sorry for a bit of a rambling response. Kind of thinking as I type.

I maintain Test and Repair records on a variety of electronic equipment. We currently have over 6,000 entries going back to June of 2018. Originally we used an Excel spreadsheet for recording the data. I transitioned it to a database. I have a search function that will look up to see if a particular Serial number has come through in the past to help the tech spot any ongoing hard to duplicate problems.

What I want to do is automate looking up that history. Ideally I want it so once you enter a serial number for a new test, any past tests would pop up on screen.

As you previously suggested, a sub form looks like what I want. But I don't see how to create one without using more than one table.

I originally had my test data spread over 4 tables. But I kept getting error where one table would update but the others would not (all used record number for the key field) This would cause incorrect data to display when I reviewed the records. I believe this was caused by comm issues between the laptop accessing the database and the server where the files were stored. I solved this problem by putting all the equipment data in one table. I have others tables for who the tester is etc. But the Serial number and past test results are all in one table.

As I think about this more, I believe what you are saying is that I need to have an entry box that asks for the serial number, then open the main and sub forms for entering new data as well as viewing historical data. Then I can enter my new test like usual?

Also not all equipment has a serial number. In that case i would be passing on a blank field. Will that be a problem?

 

[pbaldy]

If you just want to view records associated with the input serial number, how does this really rough example work (open frmMain)? The subform is based on the same data but is read-only so you wouldn't have any write conflicts.

 

[Umpire]

That is EXACTLY what I am looking for.
Now to go look at what you did so I can do it my actual database.

Thank You

 

[pbaldy]

No problem, post back if you get stuck.

 

[Umpire]

I think you can get by with a single form as long as you are willing to have it be continuous rather than a DataSheet. The Continuous view allows you to have a header on the form. In the header, you would place an unbound combo. The RowSource of the combo would be something like:

Select SerialNumber from yourtable
Group by SerialNumber
Order By SerialNumber

Then for the RowSource of the form, use a query that references the combo:

Select * from yourtable where SerialNumber = Forms!yourform!cboSerialNumber.

In the AfterUpdate event of the combo, requery the form:

Me.Requery.

So, two queries and one line of code will do it.

Thanks For responding. It looks like a subform is the way to go. It is working on my sample database. I am transitioning it my actual db now. I need to do more testing, but early indications are positive.

 

[Umpire]

Having a main form and subform bound to the same table is not advisable. Just sayin'

It also looks like you may have a design issue and this should be two tables with the SerialNumber in the parent table so It occurs only once.

I originally had it split across 4 tables. however due to being forced to connect to the server where the database is located using WiFi I kept losing sync between the tables. I had to solve this by putting the maintenance records in one table.

The subform is using datasheet view and is read only. Also we may see the same serial number multiple times over the course of a few months. And many parts do not have serial numbers. Some we assign numbers to and others we do not. This process is in a manufacturing facility so there is an endless supply of serial numbers involved. No practical way to make a table of all the serial numbers we might see at some point.

What kind of problems can I expect to see from having the main and sub forms looking at the same table?

Thanks

 

[Umpire]

Access should NEVER link to Jet/ACE BE's using WiFi.

If I knew what you were referring to I might be able to respond. I have no idea what a Jet/ACE BE is.
I am a rookie user.
I also have no choice but to run Access over wifi as there is no wired option at the work location. At this point I have not yet split the database into front and back ends. Because we have at most 3 users total and not at the same time, so it has not been an issue. I am still finalizing the entire set up. Basically fine tuning the reports etc.

I will speak to our IT about running as terminal and keeping everything on the server. That may be a viable option.

I am also unsure of what update issues you are referring to. I do use separate tables for things like which tech did the test and where some items are used. Other than that the source of the item tested, the ticket number, the part number etc will never change for a particular test. Is there some update routine that I am not aware of?

Please understand I am not trying to discount your advice. Most likely I am too inexperienced to fully grasp the importance of what you are saying.

Thank you for your input.

 

[Umpire]

Thanks for the follow up.
I like the idea of using a .bat file. I will have to consult with my IT crowd about the rest. I actually am trying to use the Access vs wifi problem as justification to have hard wires installed. I think I am making some progress in that area.

For the moment, I think I am safest by not splitting the DB yet and keeping everything on the server. Since I denormalized, there are not multiple tables to update so if comm is lost, I maintain sync. When I design a major update I can look at splitting things up again if I have the hardwire vs wifi situation under control. Definitely a lot to look at before I do MYDB ver 3.0.

 

[Umpire]

Thanks for the information. Sounds like I need to schedule a long meeting with my IT department.

 

[Umpire]

My answer to them will be, If you don't like Access, then program it in something else for me.