How do I limit the data in a split form based on information in the primary form? (1 Viewer)

Umpire

Member
Local time
Today, 04:15
Joined
Mar 24, 2020
Messages
120
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
 

Attachments

  • Test Database1.accdb
    836 KB · Views: 235

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:15
Joined
Aug 30, 2003
Messages
36,118
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

Member
Local time
Today, 04:15
Joined
Mar 24, 2020
Messages
120
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

Wino Moderator
Staff member
Local time
Today, 04:15
Joined
Aug 30, 2003
Messages
36,118
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

Member
Local time
Today, 04:15
Joined
Mar 24, 2020
Messages
120
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

Wino Moderator
Staff member
Local time
Today, 04:15
Joined
Aug 30, 2003
Messages
36,118
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.
 

Attachments

  • Test Database1.accdb
    500 KB · Views: 171

Umpire

Member
Local time
Today, 04:15
Joined
Mar 24, 2020
Messages
120
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

Wino Moderator
Staff member
Local time
Today, 04:15
Joined
Aug 30, 2003
Messages
36,118
No problem, post back if you get stuck.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2002
Messages
42,981
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.
 

Umpire

Member
Local time
Today, 04:15
Joined
Mar 24, 2020
Messages
120
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2002
Messages
42,981
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.
 

Umpire

Member
Local time
Today, 04:15
Joined
Mar 24, 2020
Messages
120
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2002
Messages
42,981
Access should NEVER link to Jet/ACE BE's using WiFi. As you can see, it creates problems. These blips that happen frequently with WiFi can result in corruption. If you lock the main form so that you can never update the bound record, you should avoid conflicts. But the WiFi is very dangerous.

Remote users should use Citrix or Terminal Services to run Access apps remotely. That is sound technology. The app actually runs on the server and so performance is very good, usually even better than what you would get on a LAN when both the FE and BE are on the same server.

Also, NEVER share the FE. Every user needs his own copy of the FE.

By denormalizing your data, you have opened yourself up to update anomalies because the same data is stored multiple times. Can you switch to Citrix or RD to get around the WiFi issue?
 

Umpire

Member
Local time
Today, 04:15
Joined
Mar 24, 2020
Messages
120
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2002
Messages
42,981
Jet (.mdb) and ACE (.accdb) are the "Access" database engines. Since they are desktop engines, they are very susceptible to drops in connection. WiFi LANs are notoriously unstable. Access cannot smoothly recover when it looses connection to the BE and this causes corruption. It is a very unstable situation. If you cannot implement the app using TS or Citrix, then your best option is to convert the BE to SQL Server and get rid of Jet/ACE. An Access FE connected to a SQL Server BE is much more stable over WiFi because SQL Server can recover sufficiently to not loose or corrupt the data. Access may freeze and you might have to shut down and reopen but the data will be safe. No matter what, Never share an unsplit app under any conditions. That is a recipe for disaster. In a multi-user environment, always distribute separate copies of the FE to each user. There are lots of posts here telling you how to do that. The simplest is to have the users open a .bat file rather than the FE on a server. The .bat file will download a fresh copy of the FE and open it. So, you never have to worry about distributing new copies of the FE. Simply replacing the server copy of the FE, will automatically propagate the new FE as each user opens the app.
 

Umpire

Member
Local time
Today, 04:15
Joined
Mar 24, 2020
Messages
120
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2002
Messages
42,981
For the moment, I think I am safest by not splitting the DB yet and keeping everything on the server.
Absolutely not. This is a recipe for disaster even for a single user at a time, let alone a multi-user app. When you open an Access database, Access brings whatever it needs across the wire to your local PC where it resides in memory. It posts back updates to the data as they happen. If anything disrupts communication during this phase, your db is toast. Not to mention, if a single user gets locked up, that impacts every other user as well.

At least split the app so that the FE can be local. That minimizes communication with the Server to gets and puts of data. Create an automatic backup for the BE that runs at least once per hour. Make sure that every user maintains an audit log so that he knows what he updated and when.

It may be cheaper to convert to SQL Server if you can get by with using the Express version or if you already have SQL Server Enterprise for some other applications. It will definitely be safer.

If you can't get past the WiFi issue, try to use Terminal Services. In that environment, ALL actions happen on the server and only pictures and keystrokes are sent over the wire and so you won't have a problem with corruption but even with TS, you still must split and each user must have his own copy of the FE to maintain stability.
 

Umpire

Member
Local time
Today, 04:15
Joined
Mar 24, 2020
Messages
120
Thanks for the information. Sounds like I need to schedule a long meeting with my IT department.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2002
Messages
42,981
They're just going to blame the problem on Access but Access isn't the problem. The problem is Jet/ACE, the desktop database engines.
 
Last edited:

Umpire

Member
Local time
Today, 04:15
Joined
Mar 24, 2020
Messages
120
My answer to them will be, If you don't like Access, then program it in something else for me. :)
 

Users who are viewing this thread

Top Bottom