VERY strange behaviour (1 Viewer)

[mounty76]

Good Morning all,

I have some really odd behaviour on a split database I have.

I have just imported a database into an already large split database. Once imported the new parts of it work fine, no problems at all. I then put only the tables into a different location, deleted the tables in the FE and linked the BE tables again.

Again it works....almost! I have a query which takes data from a table, the table (BE) and query (FE) show the correct data, I have a form that uses data from the query, the form shows all the data correctly except for one field, it is really odd, it worked before I split the DB and the BE table and FE query both show the correct data, the form is showing all the other fields expect one. I've tried deleting the field from the form and adding it back in and it stays the same, the property settings are the same as all the other fields that are showing....I'm really stumped, not sure if anyone has experienced anything like this before?

The only other way I can think of to get it working is to hold the new database in the shared network and just put some code on the main DB to open the new section as a separate DB.....not ideal but at least it would work. Any ideas most welcome before I do this!!

Cheers

 

[Ranman256]

what do you mean doesnt work?
what kinda field type is it? integer/boolean/etc
is it not showing data on the form or the query??

 

[mounty76]

It is a short text field type that uses a lookup table. I have the same field type on the same table and this works fine
Data is showing is in the query and table but not on the form.

 

[WayneRyan]

Mountain,

Typing on phone so ...
Try looking at the mSysobjects table at the connection strings.
Any surprises ???

Hth,
Wayne

 

[Gasman]

This is one of those 'How long should my piece of string be? questions'
You are going to have to load the required table and form with some data and explicit instructions on what to look for, to get to the bottom of this.

Could be anything really. What is the source for that field, does it match that in the query, you know, the usual stuff.
Why did it work before you split the DB, what else did you do, when you split the DB?
Is the field actually a table lookup field?

 

[mounty76]

Yes the field is a table lookup field. Attached is copy of the db. Sorry for it not being a great example of a db!!

When you open the DB and look at the frmRefitDraftSearch you will see in the subform a component name, everything works fine in the DB. When you split the DB and then look at the same form the component name is missing but all the other data is there?

Any help much appreciated!!

 

[Gasman]

OK, without even looking at it, you have given us a non split DB?
You said it only happened when you split the DB?
So is it still happening in the uploaded DB?

Edit: Have opened that form, absolutely no data in mainform, yet I have an entry in subform of Emergency Generator ???

Please supply the data as you have it. Do not make us do extra just to help you.

 

[Pat Hartman]

I would start by removing the lookup from the table. This is a crutch for absolute beginners. Once you know enough to create a query, you don't need the "feature" and you certainly don't need it for forms or reports. Users never look at tables or queries, only you do. And if you MUST see the lookup value, then you can create a query for yourself that joins to the lookup table.

 

[Gasman]

On initial inspection I cannot see why?
Hard to work anything out with nonsensical control names, command26, command17 etc?

You might well know how it all fits together, but just a maze to me ATM. Sorry.

 

[mounty76]

Thanks for the info guys, sorry for the badly designed db.......this isn't my day job! I sent the DB as it was as it works like this, once split it doesn't work.

I'll just put some vba to open the DB and keep it 'whole' rather than splitting it as it works like this

 

[GPGeorge]

Yes the field is a table lookup field. Attached is copy of the db. Sorry for it not being a great example of a db!!

When you open the DB and look at the frmRefitDraftSearch you will see in the subform a component name, everything works fine in the DB. When you split the DB and then look at the same form the component name is missing but all the other data is there?

Any help much appreciated!!

Lookup fields are very low on the list of features I'd willingly include in a table an Access relational database. The fact that this field in this table is causing a problem is not surprising.

That off my chest. Look at the way the values for this field are being used in forms, etc.

Lookup fields normally have two columns. One is the ID from the related table, which is stored as a Foreign Key in the current table. The other is the actual value that ID represents. In most cases, as long as you are careful to actually use ONLY the ID, and not the value field it represents, things work out okay. The problem comes when people store the value instead of the ID in the lookup field. That's when things go off the track. I'm guessing that's what happened here when you tried to relink the table.

 

[Gasman]

OK, I changed the component name to Jetski in qryrefit.
Now the form shows that in the subform even when split.?

Hopefully that might give you a clue. ?

However, do your self a favour. Give your controls meaningful names. 6 weeks down the road they will mean absolutely nothing to you, as they did to me today.

EDit: Now that works, if you try and edit that with your form, it shows the ID not the name (consequence of table lookups)
I know you have them all over the place, but you would be better off getting rid of them for this reason alone.

Hard to take in, I know, as you have come this far, but it will help you so much in the future. Good luck with it anyway.

 

[mounty76]

Thanks guys, easy when you know how I'm sure, I'll keep it as a separate standalone DB and open it with vba. How much do you guys charge for building DB's haha

 

[Pat Hartman]

I'll keep it as a separate standalone DB and open it with vba.

That would be a poor solution.

 

[mounty76]

Agreed but just don't have the time to start from scratch, sorry!

 

[Pat Hartman]

Don't apologize to us. You're the one ignoring the expert advice and you're the one who will live with the consequences. I personally have enough scars to tell you that without a doubt, it is a bad idea to add the second story when your foundation is crumbling.

 

[mounty76]

Expert advice is easy to follow if your an expert, I don't follow the below

This is a crutch for absolute beginners. Once you know enough to create a query, you don't need the "feature" and you certainly don't need it for forms or reports. Users never look at tables or queries, only you do. And if you MUST see the lookup value, then you can create a query for yourself that joins to the lookup table.

 

[Gasman]

Expert advice is easy to follow if your an expert, I don't follow the below

This is a crutch for absolute beginners. Once you know enough to create a query, you don't need the "feature" and you certainly don't need it for forms or reports. Users never look at tables or queries, only you do. And if you MUST see the lookup value, then you can create a query for yourself that joins to the lookup table.

In simple terms. YOU create the lookup with a query join.
Then you know that the data, is as presented, ie that the component name is actually the component name and not anything else, like really the component ID.

Once you get your head around that, then the rest is easy.

 

[mounty76]

Any chance to show me a sample DB with a simple explanation of this, struggling to get past this

 

[mounty76]

I'm assuming you mean something like the attached? If so how do I then chose an item on the form from the equipment list that then stores the compID in the refit table?