Form jumping around in records when i field is typed

[mike60smart]

As i said, this is a database I inherited and I don't want to start back from scratch to redo it. I'm trying to work with what is there. Do you recommend i that the special characters out? It would take a while to follow the paths to make sure it go everything.

All of the items I listed are relatively easy to fix.

Best to fix now.

 

[dullster]

Hi
As suggested by Mike Krailo your Transactions Form should look like that shown in the attached screenshot.

Except that i only want the information for a check and not a client.

 

[mike60smart]

Except that i only want the information for a check and not a client.

But as mention by Mike the Form dealing with Transactions is wrong. It needs to be fixed.

 

[June7]

If you retain punctuation/special characters just keep in mind that MUST use brackets around names in queries and many code situations.

I agree that the form/subform arrangement makes no sense.

 

[dullster]

I happened to open that form and typed in a few digits. Nothing happened.
I looked at the query source and use 8931.
Yiu cannot use a bound control to search for data.
You have a lot of empty subform records.

But as mention by Mike the Form dealing with Transactions is wrong. It needs to be fixed.

As i said, i want it by Check number not client #

 

[dullster]

All of the items I listed are relatively easy to fix.

Best to fix now.

Not what I wanted to do, but I understand. I will make the changes.

 

[Pat Hartman]

I have a form that contains a subform. They are linked by the Check #, so that the check number updates or creates the records in the subform.

Your original problem seems to not be repeatable but there is a lot of work needed to fix the schema as well as the forms. In addition to and to emphasize earlier comments.

1. NO main form should EVER be bound to the same table as one of its subforms.
2. Search fields should NEVER be bound

I'm trying to bring it up to a more user friend database. I don't want to reinvent the wheel. I want to be able to update the databases around the state and offer a more user friendly product.

Under the covers the schema and interface are poor. I hope the "professor" wasn't teaching database design What is it that you think needs changing? Technically poor object naming practices don't need to be corrected but for a database of this size, it shouldn't take more than a couple of hours and so may be a good investment of your time because it will let you become familiar with the code without actually changing any logic.

What you really need to fix are the relationships because enforcing RI is very important. You also need to add validation code to all forms to prevent bad/empty records from being added and you need to fix the search/filter feature on the form you were having trouble with so the checkNumber in the main form is not bound. You need to remove the transaction table from the main form's RecordSource and the main form should be only the client info. Then you need a search/filter feature that filters the subform by check and/or other fields.

Is everyone sharing the same BE or does each town have it's own shared BE?

Changing the BE for a distributed app like this one is tricky. What you need to do is to create an Access app that modifies the schema - once. To do this, the first change I would make to the app is to add two tables. One to the FE and One to the BE. These identify the database FE/BE version. Then in the opening form, you check the two tables to ensure that the versions match. This becomes critical once you start changing the BE for real. You don't want the wrong FE trying to work with your updated BE and vice versa.

How many installations are we talking about? If the number is small enough, you might want to control all the upgrades by doing them yourself over one or two weekends via remote connections. If that isn't manageable and you have to trust an admin user at each site to do the upgrade, you need to make it foolproof.

Anyway, I have an app that is sold to the public where I use this technique so I can talk in more detail about how I handle things as you get to the point of needing actual details. I may be able to send you an updater for a BE that changes it from versionX to versionY. I'll look at one to see if the client would have a problem with me sharing it. It wouldn't work for you of course but you could see how I did the updates to tables. I did decide at the beginning that the distribution of updates would be difficult enough so I wouldn't be deleting data or columns or renaming columns, but then my naming standards were good from the start. So, all upgrades, added tables/columns and sometimes populated tables with new lookup values that the users didn't control. Sometimes the upgrades added/deleted indexes and relationships.

 

[Mike Krailo]

As i said, i want it by Check number not client #

A main form is the ONE part in a one to many relationship and represents one single record in a given table. The MANY in your subform is based off of tblTransactionsDemo right? One client can have many checks/payment transactions. That makes sense. What doesn't make sense is linking a main form by Check# to transaction in the same table that also has the same exact Check# per Amount in the transaction. That makes zero sense but you keep saying you want it that way.

Stated differently, how can one single check# have many payment Amounts? Please explain to us what the ONE side is exactly and what the MANY side is supposed to be in plain English? Right now it's set up as one single check can have many transaction amounts. This is incorrect because looking at your data, the check# column is not unique. The main form has to be linked by a unique record ID or unique client name possibly. The one side must be unique.

Check# 8784 for example has many transaction ID's and is not a unique value suitable for your main form. Doing a simple search on that check number proves that. I can only conclude that you do not under stand how a simple ONE to MANY relationship is correctly created.

 

[Gasman]

1. NO main form should EVER be bound to the same table as one of its subforms.

I would have to say that there can be exceptions.?
The Emulated Split Form does exactly that.

Two forms with same record source prevent writing conflict

Dear friends, I'll keep it short and sweet. I have two forms, with the same record source. Say form A and form B. Form-A is the main form. But Form-B goes into more detail. Both forms are showing completely different fields on it. But once i entered the details in Form-B, close it, and want...

www.access-programmers.co.uk

 

[Mike Krailo]

Good point @Gasman

 

[Pat Hartman]

I would have to say that there can be exceptions.?

You do it with two subforms to avoid the issue and the list subform is not updateable. The MS version handles this behind the scenes to avoid conflict probably by making the mainform unbound so there is only one recordset open. This implementation isn't using the list subform to filter the bound main form. It is doing just the opposite. the logic is backwards and that is why the subform will jump around because you are changing the value of a control in the main form which is bound to the subform. If you type in a check number that doesn't exist, it adds a new row to the subform with no other data. That is where the empty records are coming from.

The transaction table needs to be removed from the main form's RecordSource and the check # control needs to be unbound. It can be a combo so it can find existing checks.

There also isn't a table where CheckNum is unique. You need a check table so you can store the check number, check amount, and bank account. Then you have a child table where you split the dollars into multiple accounts.

As I said earlier, the poor naming convention is a red herring. It is annoying but doesn't cause errors per se.

 

[mike60smart]

Not what I wanted to do, but I understand. I will make the changes.

Hi
I have corrected all of the points I had noted previously.
I believe I have also updated all of the queries that run your Reports.

See if the db now behaves as it should.

Note there may be some objects that do not run as expected.