Form jumping around in records when i field is typed

[dullster]

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. As i type in a new check number, the subform records jump around to the old check number as I type. How can stop this if it is a new record/check?

 

[Mike Krailo]

the subform records jump around to the old check number as I type. How can stop this if it is a new record/check?

You can put some If Me.NewRecord Then logic in VBA to make it stop doing whatever you are currently doing. Check the On Current event.

 

[June7]

Why does subform "jump around" - what code is behind control where you type check number? Is this an UNBOUND control?

 

[MajP]

My guess is that you have code in the on change event of the check# doing some kind of filtering or ordering. Is the subform linked by Master Child filelds in the subform control or are you filtering the subform using your own vba?

 

[mike60smart]

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. As i type in a new check number, the subform records jump around to the old check number as I type. How can stop this if it is a new record/check?

Can you upload a copy of the database?

 

[dullster]

You can put some If Me.NewRecord Then logic in VBA to make it stop doing whatever you are currently doing. Check the On Current event.

Why does subform "jump around" - what code is behind control where you type check number? Is this an UNBOUND control?

It is not bound to anything. I think it jumps around because i am typing in an existing check # and it's bringing up that record.

 

[dullster]

My guess is that you have code in the on change event of the check# doing some kind of filtering or ordering. Is the subform linked by Master Child filelds in the subform control or are you filtering the subform using your own vba?

Yes, the subform is linked through Master Child by Check #

 

[dullster]

Can you upload a copy of the database?

How would i do that? As an attachment?

 

[Pat Hartman]

You only addressed part of MajP's comment. It helps us all if you address all comments/questions.

To upload a file use the attachment option below the box where you type your message.

 

[dullster]

Can you upload a copy of the database?

Yes, the subform is linked through Master Child by Check #

I do not have an on change.

 

[dullster]

How would i do that? As an attachment?

 

[Gasman]

And instructions on how to recreate? :-(

 

[dullster]

And instructions on how to recreate? :-(

open the Transactions Form View. When i started typing in a check number, Information would jump around in the subform. For some strange reason, now i can't recreate it.
I want to give you some information about the database. I did not create this database. It was created by a Professor in the last 1990's and is very simple. It has been used by over 40 townships in Counties in Wisconsin for completing reports, etc. 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.

 

[Gasman]

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.

 

[mike60smart]

open the Transactions Form View. When i started typing in a check number, Information would jump around in the subform. For some strange reason, now i can't recreate it.
I want to give you some information about the database. I did not create this database. It was created by a Professor in the last 1990's and is very simple. It has been used by over 40 townships in Counties in Wisconsin for completing reports, etc. 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.

Hi
I have taken a look at the database and have the following issues:-

Field names should not have characters ~$/-!"£%^&*()
Each table should have a PK which is numeric and not text
A number of tables have Lookup Fields which should not be used
Unable to Enforce Referential Integrity between both tblTransTypes & tblDemoClients.
When you try to establish Referential Integrity the following error tells me that data in tblDemoTrans violates referential integrity.

 

[June7]

As noted by Gasman, controls used to enter search criteria must be UNBOUND - otherwise you change data in record.

 

[Mike Krailo]

I don't think he is searching for anything, he is trying to add a new record which involves a new check number in the main form and wondered why the subform data was displaying. That's because of Check# linking to Check# in the subform. The big problem I see is that the main form is based off of tblTransactionsDemo table and the subform is based off of the same table. I would think that the main form would be based off of tblDemoClients and the subform based off of tblTransactionsDemo and linked by ClientShortName which is the primary key for the clients. That's another can of worms as well. Should be using an autonumber for ClientID. Really bad idea to use a clients name as the primary key.

These are his unchanged table relationships. The form in question is linked by Check# which makes no sense to me. Does one check# relate to many Amounts? Very strange. Just need more clarification on the desired workings of that form.

 

[mike60smart]

open the Transactions Form View. When i started typing in a check number, Information would jump around in the subform. For some strange reason, now i can't recreate it.
I want to give you some information about the database. I did not create this database. It was created by a Professor in the last 1990's and is very simple. It has been used by over 40 townships in Counties in Wisconsin for completing reports, etc. 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.

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

 

[dullster]

Hi
I have taken a look at the database and have the following issues:-

Field names should not have characters ~$/-!"£%^&*()
Each table should have a PK which is numeric and not text
A number of tables have Lookup Fields which should not be used
Unable to Enforce Referential Integrity between both tblTransTypes & tblDemoClients.
When you try to establish Referential Integrity the following error tells me that data in tblDemoTrans violates referential integrity.

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.

 

[dullster]

I happened to open that form and typed in a few digits. Nothing happened.

I don't think he is searching for anything, he is trying to add a new record which involves a new check number in the main form and wondered why the subform data was displaying. That's because of Check# linking to Check# in the subform. The big problem I see is that the main form is based off of tblTransactionsDemo table and the subform is based off of the same table. I would think that the main form would be based off of tblDemoClients and the subform based off of tblTransactionsDemo and linked by ClientShortName which is the primary key for the clients. That's another can of worms as well. Should be using an autonumber for ClientID. Really bad idea to use a clients name as the primary key.

These are his unchanged table relationships. The form in question is linked by Check# which makes no sense to me. Does one check# relate to many Amounts? Very strange. Just need more clarification on the desired workings of that form.
View attachment 119058

Yes, one check could be for payroll and deductions. example