Records missing from form view (1 Viewer)

[james_liv]

Hi,

Can anyone offer any help.

I've got a multipart database with several links.

I enter a record into the database form and then try to see it later and its gone.

If I go into field view then all the data is there but the record is nowhere to be found in the form.

I'm puzzled and struggling to understand why.

Can anyone help,

Many thanks

 

[theDBguy]

Hi. What is the Record Source of the Form?

 

[The_Doc_Man]

Have you saved the record yet? The form holds data as long as it is open but won't write it unless you take some kind of step to force it. If you don't save it, it is not in the underlying recordsource. Or did I misunderstand your description?

 

[Pat Hartman]

If the form is filtered and you add a record that won't be brought up by that filter, you won't see the new record.

 

[james_liv]

Hi. What is the Record Source of the Form?

In the main form (as there are many) if I go into the property sheet of the form and then go into record source it states,

SELECT [tblCustomerInfo].[Surname], [tblSalesSheet].[Job No], [tblSalesSheet].[BkgDate], [tblSalesSheet].[BkgTime], [tblSalesSheet].[AM/PM], [tblSalesSheet].[Staff Init], [tblSalesSheet].[Job], [tblSalesSheet].[Office Use Only], [tblSalesSheet].[Driver], tblSalesSheet.[InvoiceYN], tblSalesSheet.[InvNum], [tblSalesSheet].[Vat Rate], [tblSalesSheet].[Vat (inc)], [tblSalesSheet].[S Total], [tblSalesSheet].[Deposit], [tblSalesSheet].[Misc+], [tblSalesSheet].[Invoice Total], [tblSalesSheet].[Parts], [tblSalesSheet].[Labour], [tblSalesSheet].[Paid/Not], [tblSalesSheet].[P Method], [tblSalesSheet].[Print Inv?], [tblSalesSheet].[Paid Date], [tblCustomerInfo].[DoorNo], [tblCustomerInfo].[Postcode], [tblCustomerInfo].[Title], [tblCustomerInfo].[Address], [tblCustomerInfo].[Address 2], [tblCustomerInfo].[Town], [tblCustomerInfo].[City], [tblCustomerInfo].[HomeNumber], [tblCustomerInfo].[WorkNumber], [tblCustomerInfo].[OtherNumber], [tblCustomerInfo].[Customer Info], tblSalesSheet.[Book Del?] FROM tblCustomerInfo INNER JOIN tblSalesSheet ON [tblCustomerInfo].[Customer Number] =[tblSalesSheet].[Job No];

Have you saved the record yet? The form holds data as long as it is open but won't write it unless you take some kind of step to force it. If you don't save it, it is not in the underlying recordsource. Or did I misunderstand your description?

Yes, I click the save button. Also I get to the end of the form fields and it rolls onto the next record to enter. In some cases I enter a second and third record.
I go out of the form and then go back and those records are not there but if I go into the tables then the data is there.

If the form is filtered and you add a record that won't be brought up by that filter, you won't see the new record.

No, its not filtered.

Thanks for the help. Its absolutely drove me insane trying to work it out today and ive got nowhere.

 

[pbaldy]

Also make sure the form's Data Entry property isn't Yes, or that it wasn't open in add mode.

 

[theDBguy]

In the main form (as there are many) if I go into the property sheet of the form and then go into record source it states,

SELECT [tblCustomerInfo].[Surname], [tblSalesSheet].[Job No], [tblSalesSheet].[BkgDate], [tblSalesSheet].[BkgTime], [tblSalesSheet].[AM/PM], [tblSalesSheet].[Staff Init], [tblSalesSheet].[Job], [tblSalesSheet].[Office Use Only], [tblSalesSheet].[Driver], tblSalesSheet.[InvoiceYN], tblSalesSheet.[InvNum], [tblSalesSheet].[Vat Rate], [tblSalesSheet].[Vat (inc)], [tblSalesSheet].[S Total], [tblSalesSheet].[Deposit], [tblSalesSheet].[Misc+], [tblSalesSheet].[Invoice Total], [tblSalesSheet].[Parts], [tblSalesSheet].[Labour], [tblSalesSheet].[Paid/Not], [tblSalesSheet].[P Method], [tblSalesSheet].[Print Inv?], [tblSalesSheet].[Paid Date], [tblCustomerInfo].[DoorNo], [tblCustomerInfo].[Postcode], [tblCustomerInfo].[Title], [tblCustomerInfo].[Address], [tblCustomerInfo].[Address 2], [tblCustomerInfo].[Town], [tblCustomerInfo].[City], [tblCustomerInfo].[HomeNumber], [tblCustomerInfo].[WorkNumber], [tblCustomerInfo].[OtherNumber], [tblCustomerInfo].[Customer Info], tblSalesSheet.[Book Del?] FROM tblCustomerInfo INNER JOIN tblSalesSheet ON [tblCustomerInfo].[Customer Number] =[tblSalesSheet].[Job No];



Yes, I click the save button. Also I get to the end of the form fields and it rolls onto the next record to enter. In some cases I enter a second and third record.
I go out of the form and then go back and those records are not there but if I go into the tables then the data is there.


No, its not filtered.

Thanks for the help. Its absolutely drove me insane trying to work it out today and ive got nowhere.

Hi. Since your Record Source is using an INNER JOIN, it might be possible the new record you just added doesn't have a matching Customer Number=Job No. Does it?

 

[james_liv]

Also make sure the form's Data Entry property isn't Yes, or that it wasn't open in add mode.

It was turned on and that seems to have resolved it. Ill try to experiment some more. I'm sure its still losing pieces of information but ill experiment.

Hi. Since your Record Source is using an INNER JOIN, it might be possible the new record you just added doesn't have a matching Customer Number=Job No. Does it?

They are auto generating numbers. I've just checked however and yes, they do match.

So in short, I think I had the Data Entry Property as Yes. Ive changed it and information is now being stored again. Ill play around with it and see if im losing data still.

Thank you.

 

[theDBguy]

It was turned on and that seems to have resolved it. Ill try to experiment some more. I'm sure its still losing pieces of information but ill experiment.

They are auto generating numbers. I've just checked however and yes, they do match.

So in short, I think I had the Data Entry Property as Yes. Ive changed it and information is now being stored again. Ill play around with it and see if im losing data still.

Thank you.

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

You should think about changing the Inner Join to a Left Join. The Inner join will miss records if JobID is optional but the Left join will not.

 

[pbaldy]

Happy to help!

 

[james_liv]

You should think about changing the Inner Join to a Left Join. The Inner join will miss records if JobID is optional but the Left join will not.

Both of the ID's are autonumbers when you first type into the form so should be ok.
Ill happily change it if it would help thou but how do I do that? Do I need to given they are both autonumbers?

Many thanks

 

[Isaac]

Pardon me for jumping in, but just to answer your last question, I believe you would just change the recordsource sql where it says INNER JOIN to LEFT JOIN. I'm not clear on exactly how you're setting that recordsource, but most likely you can just go into the form's property pane, find the Record Source property, click the ellipsis button to the right, change the view (upper left hand corner), to SQL, and change the word INNER to LEFT. Or of course, you can right-click on the Join line in the query's Design view (without changing to SQL view), and edit the Join properties.

But you should think about this before you do it. Your inner join is doing exactly what it was designed to do - return ONLY records where the joined columns exist in both tables. It is not necessarily the case that this is wrong. Only you can answer that. If you want the inner join to perform like that (only show records where the joined column data exists in both tables), then carry on. IF you want to allow for MORE records to be returned--including records where the JobID does not exist--then change it to a left join. It could completely change the output records returned from the query. You may or may not want these extra records. It is just something to consider.

 

[james_liv]

Pardon me for jumping in, but just to answer your last question, I believe you would just change the recordsource sql where it says INNER JOIN to LEFT JOIN. I'm not clear on exactly how you're setting that recordsource, but most likely you can just go into the form's property pane, find the Record Source property, click the ellipsis button to the right, change the view (upper left hand corner), to SQL, and change the word INNER to LEFT. Or of course, you can right-click on the Join line in the query's Design view (without changing to SQL view), and edit the Join properties.

But you should think about this before you do it. Your inner join is doing exactly what it was designed to do - return ONLY records where the joined columns exist in both tables. It is not necessarily the case that this is wrong. Only you can answer that. If you want the inner join to perform like that (only show records where the joined column data exists in both tables), then carry on. IF you want to allow for MORE records to be returned--including records where the JobID does not exist--then change it to a left join. It could completely change the output records returned from the query. You may or may not want these extra records. It is just something to consider.

Thanks,

Tbh what that join was meant to do doesnt really work and so I might remove it.

What I really wanted was when I typed a postcode and door number it would auto populate the rest of the address if there was matching records in the customer information database.
This never worked and so I removed the feature and moved on.

Thinking now I could probably remove the link altogether.

 

[Pat Hartman]

It is not necessarily the case that this is wrong.

Joins in queries used as the RecordSource for forms should be Left joins UNLESS the FK is a required field. Whenever the FK is optional, the join needs to be a Left join or you will never see the records with the unspecified FK in order to fill it in.

if there was matching records in the customer information database.

This tells us that the field is optional so you do want to change to a Left Join. The Left join will bring up any matching data and will allow you to add new records as needed. Generally, I would not alllow this behavior since it it is dangerous because it opens up the possibility of people accidentally changing data from another table.

It is the autopopulate part of your description that I question. Typically, you would only store the FK in the main table. You would not store the lookup data. What do you want to happen if the postcode and door number do not exist?

 

[Isaac]

Well I certainly hope you would not be designing something that allows for orphaned child records, (where jobs couldn't be related to their parent customer), but I guess if you were, you'd have to accommodate that.

 

[james_liv]

OK, so I think there's a misunderstanding here. My fault.

The customer ID and Job number are fields which are used to join. They are not optional and they both autonumber so they always match - obviously these are system generated for every record.

Originally the idea was for the postcode and door number to be the join so that when you typed those in then it would retrieve the matching customer address and populate the fields automatically. It never worked and I couldn't get it to populate so I used the job number and customer number instead.

That said, without the ability to retrieve the address matching the postcode and door number having a separate custoner address database isn't actually of any benefit anyway and may as well be part of the main database. However, I might revisit this later on but I'm struggling with other issues right now.

 

[Pat Hartman]

Isaac, I was not referring to situations where the parent would be missing. If a Job requires a customer, then the customer FK must be required. It can never be null. That is what I said. However, the sales agent might not necessarily be assigned when the job is created so the SalesRepID might be null. If you used an inner join in this query, you would not return records where the SalesRepID is null. That is the situation I was referring to.

It never worked and I couldn't get it to populate so I used the job number and customer number instead.

It never worked because you were using an Inner Join. If you switch to an Outer Join (Left to be specific), your results should improve.

 

[james_liv]

Isaac, I was not referring to situations where the parent would be missing. If a Job requires a customer, then the customer FK must be required. It can never be null. That is what I said. However, the sales agent might not necessarily be assigned when the job is created so the SalesRepID might be null. If you used an inner join in this query, you would not return records where the SalesRepID is null. That is the situation I was referring to.

It never worked because you were using an Inner Join. If you switch to an Outer Join (Left to be specific), your results should improve.

I've ended up having to incorporate the customer database into the main database.
When I was searching for invoice numbers the system seem to switch the customer each invoice was for.
I think it was because both the booking system and the sale system used their individual job numbers as joins connected to the customer number and when I switched from bookings and sales it was switching the addresses.

So yes, in short I think that joining two different systems via the same customer number was causing serious problems.

I've not finished my rework yet but fingers crossed it will resolve my problem.

 

[Pat Hartman]

When I was searching for invoice numbers the system seem to switch the customer each invoice was for.

That's because you were using the bound field for "searching". You weren't actually searching. You were actually updating the bound field. Search controls are ALWAYS unbound.

You should probably fix your tables again so they are properly normalized.