By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,700 Members | 1,349 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,700 IT Pros & Developers. It's quick & easy.

Change RecordSource for forms

P: 4
Hi,

I have a number of tables with the same structure they are all lookup tables. I went this way because I couldn't stop invalid data from being loaded (data is imported via transferspreadsheet). Even though I have set up validation in the spreadsheet, I wanted to make sure that if that gets beaten, that the database validation will stop the invalid data.

Could someone tell me the best way to do this using the single table approach? I do have combo boxes set up for the fields (and other validations), but was able to load invalid data with all of these set.

My alternative approach has been to set up a form with a combo box containing all of the lookup tablenames. I thought that I would just have a after update event which sets the form recordsource to the value in the drop-down list. This kind of worked, but in this form, I have two views of the same table - a single form with a datasheet version below it. (Overkill, but I am only allowing edits appends etc to the single form view - that datasheet view is there for quick view.)
The datasheet only shows a single record (the same as that in the single form). I have checked that there are no filters or parent/child relationships, and tried the me.requery, but can't see what else could be going wrong. Any suggestions?

Thanks,

Jason
Sep 27 '06 #1
Share this Question
Share on Google+
4 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi,

it is possible to store the imported data from Excel in one table!

In fact if this is a simmilar structure this is better!

But the validation when importing your data depends on the problematic fileds in Excel... You have pb with dates? numbers? or what?

Maybe a small functions that correct the data from Excel can help you!

:)
Sep 30 '06 #2

P: 4
Hi PEB,

I started with a single table that had all the lookup values in it and referenced them appropriately in the combo boxes in the table design. I found this fine if data is entered into the database or forms, but when I imported data through excel (transferspreadsheet) data went into the tables like I had done nothing.

What I have done since is set up a whole heap of lookup tables that have a one to many relationship with enforce data entegrity checked. This stops invalid data, and doesn't matter where or how you get the data. I thought this would be the messy way of doing things.

Jason
Oct 5 '06 #3

P: 4
Would having all these lookup tables slow down the opening of forms that have data referencing them? I have some forms that have 5 or 6 sub forms including some crosstab queries so anything that I can do to speed up opening these forms would help (the standard on the machines that will be using this database takes 4 or 5 seconds to open even though cycling through records is almost intantaneous).
Oct 5 '06 #4

PEB
Expert 100+
P: 1,418
PEB
In fact to prevent values that aren't correct your way to do it is good...

Maybe your problem are the missed records? And you want to vizualize them and make the users to pay attention about...?

If you import data from Excel, linking your tables this can be possible... To obtain the records that haven't been imported...

You can use the find Unmatched query wizard for this task!

:)
Oct 8 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.