Unable to enter new data (Access 2000)

Topic

New Reply

Today I have been advised that I can post my dilemma on Woody’s Lounge and get advise of how I can resolve the serious issue I am having within MS Access 2000.

I am a self taught new user of access. I have developed a dbase with 8 tables and lookups linking to a central table.

The eight tables relate to personnel, data of competitors, i.e. Christian names, surnames, address, town, zip, etc., etc. I was hoping have Data entry by using a form. Whilst rattling the keys data entry appears to be going alright, when I action a query and/or go to the central table there is no data entered except for the sequential self numbering record ID. Yet data can be entered directly into the Central table when it is opened as a table.

As I said before I am a new users, so setting up the links, developing the forms and the lookups I have made extensive use of the Wizards. I have also used performance analysis, repair and condense, nothing seems to repair or suggest a possible repair to allow new data to be entered into this data base by using a form.

Everything else (queries, reports etc) appear to be working OK on Historical data imported into the database from Excel

In summary, my dilemma is how can I make this database accept the details of a new entrant while retaing the lookup facility active for individuals who have been here before.

Keenly awaiting your assistance with positive responses.
Harry

Reply | Quote

Replies

Welcome to Woody’s Lounge.

What is the Record Source property of your form? If it is the name of a query based on multiple tables, or a text string that starts with SELECT, the form may not be updatable. We would have to know more about the Record Source and about the tables to say for sure.

Try setting the Record Source property to the name of the central table. You can use combo boxes to let the user choose values dependent on the other tables.

Please feel free to ask further questions. It helps if you can provide detailed information.

Reply | Quote

G’day Hans
First thanks for your prompt reply and your welcome to the Lounge room as I continue this sharp learning curve that I have been struggling with for over 12months now. I could imagine people thinking it is stupity on my part, but it is probably more a case of tenacity within my character that I have just got to get it to work to conserve space, hence dbase project.

In reply to your inquiry, If I remember correctly and I am pretty certain that I built the form from a query linking fields of all the tables to the central tbl with tbl IDs. How do I check Record Source property of the data entry form – sorry as I said I am self taught and not that proficient with Access as yet!!!

Yes for the individual fields in the data entry form I have already deployed Combo Boxes.

You please keep asking the questions and I’ll go to the dbase to see if I can find the answers for you (US – that is all of us in the lounge room)

Re: NYIntensity’s response they each of the tables have unique ID key and the central tble has a unique data field called “Rod_No” as there is only one such number issued to each competitor this year, but if he comes back next year he will be issued with a completely new Rod #.

Again thanks for your prompt reply, I now have renewed enthusiasm that we will get on top of this one and learn big heaps in the process.
Harry

Reply | Quote

To check the record source, open the form in design view. Activate the Properties window (View | Properties) and activate the Data tab in that window. The first property in that tab is the Record Source.

Reply | Quote

Hans
The property record source is as follows: SELECT [ROD No].[Rod No], [ROD No].[SURNAME_ID], [ROD No].[CHRISTAIN_ID], [ROD No].[STREET_ID], [ROD No].[TOWN_ID], [ROD No].[SECTION_ID], [ROD No].[CLUB_ID], [ROD No].[YEAR_ID], [ROD No].[AWARE_ID], [AWARE].[TV], [AWARE].[Radio], [AWARE].[Poster], [AWARE].[WoM], [AWARE].[Regular], [AWARE].[Mail], [AWARE].[ID] FROM AWARE INNER JOIN [ROD No] ON [AWARE].[ID]=[ROD No].[AWARE_ID];

I am not clever enough to write all this, it was done by creating the form using wizard.

Reply | Quote

Thanks. This Record Source is an SQL statement, representing a query. The query is based on two tables, AWARE and ROD No, linked on the ID vs AWARE_ID field. I don’t know which one of these is the “main” table (probably ROD No?); the link field in the “main” table should be the primary key of that table. You can check this by opening the main table in design view and inspecting the Indexes window.

Reply | Quote

Hans
Yes the Main Table (central tbl) is ROD No

The primary Key is called just “ID”. Wish I knew how to capture a pic of that page to send you.

[ROD No]![ID] or [ID] as it is shown on the table property page it has displayed beside it the only key symbol on that page. The reported Feild Properties are as follows “Long Integer”; “Increment” & for indexed it is “Yes (No Duplicates)”. Lookup is blanked out.

Shall catch you tommorrow if I can, or read with great expectations your reply & suggestions. The whole data base zipped up is nearly 450 Kbs if that is easier for you. But by working slowly through everything on the board in the lounge room, my knowledge is being expanded and/or reaffirmed, plus there is an opportunity for others to maybe learn something as well.

Thanks very much.
Hoo Roo & Sleep Well
Harry

Reply | Quote

That sounds OK. The downside is that it’s not clear to me why the form shouldn’t be updatable.

If you want to post a copy of your database, it will have to be less than 100 KB zipped. See post 401925 for instructions on how to cut down the size of the database.

Reply | Quote

That sounds OK. The downside is that it’s not clear to me why the form shouldn’t be updatable.

If you want to post a copy of your database, it will have to be less than 100 KB zipped. See post 401925 for instructions on how to cut down the size of the database.

Reply | Quote

To capture a screenshot, press Alt-PrntScrn….then you can paste into a word document or paint, etc..

Reply | Quote

To capture a screenshot, press Alt-PrntScrn….then you can paste into a word document or paint, etc..

Reply | Quote

Hans
Yes the Main Table (central tbl) is ROD No

The primary Key is called just “ID”. Wish I knew how to capture a pic of that page to send you.

[ROD No]![ID] or [ID] as it is shown on the table property page it has displayed beside it the only key symbol on that page. The reported Feild Properties are as follows “Long Integer”; “Increment” & for indexed it is “Yes (No Duplicates)”. Lookup is blanked out.

Shall catch you tommorrow if I can, or read with great expectations your reply & suggestions. The whole data base zipped up is nearly 450 Kbs if that is easier for you. But by working slowly through everything on the board in the lounge room, my knowledge is being expanded and/or reaffirmed, plus there is an opportunity for others to maybe learn something as well.

Thanks very much.
Hoo Roo & Sleep Well
Harry

Reply | Quote

Thanks for that Jeremy – Hey I love this lounge – I am LEARNING

Hans See below if I can get the pic attached now.

Reply | Quote

Thanks. And is ID the primary key in the AWARE table?

Reply | Quote

Thanks. And is ID the primary key in the AWARE table?

Reply | Quote

Thanks for that Jeremy – Hey I love this lounge – I am LEARNING

Hans See below if I can get the pic attached now.

Reply | Quote

Thanks. This Record Source is an SQL statement, representing a query. The query is based on two tables, AWARE and ROD No, linked on the ID vs AWARE_ID field. I don’t know which one of these is the “main” table (probably ROD No?); the link field in the “main” table should be the primary key of that table. You can check this by opening the main table in design view and inspecting the Indexes window.

Reply | Quote

Hans
The property record source is as follows: SELECT [ROD No].[Rod No], [ROD No].[SURNAME_ID], [ROD No].[CHRISTAIN_ID], [ROD No].[STREET_ID], [ROD No].[TOWN_ID], [ROD No].[SECTION_ID], [ROD No].[CLUB_ID], [ROD No].[YEAR_ID], [ROD No].[AWARE_ID], [AWARE].[TV], [AWARE].[Radio], [AWARE].[Poster], [AWARE].[WoM], [AWARE].[Regular], [AWARE].[Mail], [AWARE].[ID] FROM AWARE INNER JOIN [ROD No] ON [AWARE].[ID]=[ROD No].[AWARE_ID];

I am not clever enough to write all this, it was done by creating the form using wizard.

Reply | Quote

Hans

Are you still suggesting that I change the Record Source for the form to the name of the Central Table. “Rod No”.

I am going to have to call it a night as the time is now 1/4 to 2 AM, have to be up for work in 5 hours, the missus is already starting to raise her voice saying BED TIME!!

I look forward to hopefully tommorrow night to continue with you as we resolve this issue.

Hoo Roo for now
Harry

Reply | Quote

Hans

Are you still suggesting that I change the Record Source for the form to the name of the Central Table. “Rod No”.

I am going to have to call it a night as the time is now 1/4 to 2 AM, have to be up for work in 5 hours, the missus is already starting to raise her voice saying BED TIME!!

I look forward to hopefully tommorrow night to continue with you as we resolve this issue.

Hoo Roo for now
Harry

Reply | Quote

To check the record source, open the form in design view. Activate the Properties window (View | Properties) and activate the Data tab in that window. The first property in that tab is the Record Source.

Reply | Quote

G’day Hans
First thanks for your prompt reply and your welcome to the Lounge room as I continue this sharp learning curve that I have been struggling with for over 12months now. I could imagine people thinking it is stupity on my part, but it is probably more a case of tenacity within my character that I have just got to get it to work to conserve space, hence dbase project.

In reply to your inquiry, If I remember correctly and I am pretty certain that I built the form from a query linking fields of all the tables to the central tbl with tbl IDs. How do I check Record Source property of the data entry form – sorry as I said I am self taught and not that proficient with Access as yet!!!

Yes for the individual fields in the data entry form I have already deployed Combo Boxes.

You please keep asking the questions and I’ll go to the dbase to see if I can find the answers for you (US – that is all of us in the lounge room)

Re: NYIntensity’s response they each of the tables have unique ID key and the central tble has a unique data field called “Rod_No” as there is only one such number issued to each competitor this year, but if he comes back next year he will be issued with a completely new Rod #.

Again thanks for your prompt reply, I now have renewed enthusiasm that we will get on top of this one and learn big heaps in the process.
Harry

Reply | Quote

Welcome to Woody’s Lounge.

What is the Record Source property of your form? If it is the name of a query based on multiple tables, or a text string that starts with SELECT, the form may not be updatable. We would have to know more about the Record Source and about the tables to say for sure.

Try setting the Record Source property to the name of the central table. You can use combo boxes to let the user choose values dependent on the other tables.

Please feel free to ask further questions. It helps if you can provide detailed information.

Reply | Quote

I’d like to take a shot at this…if I’m undermining your intelligence or anything of the sort that is NOT my intention…..

Have you made sure that your input fields in your form are linked to the columns/fields you want to enter the data into? If you’re using separate tables (I.E. a separate table for things such as address, one for Christian names, etc.) and you’re inputting the data in the same form, you need to have some sort of common denominator between them for access to recognize them as belonging to the same person (primary key, social security number, etc)…something different for all. With that primary key, Access can then know to export the info from your form into specific columns throughout your tables.

That’s how I’d start with it…but I consider myself a beginner user and I’m posting not only to try to help, but also for my own knowledge….

Reply | Quote

Jeremy

To follow our progress go back to the initial thread starting point (a reponse to your suggestion is included there too)

Thanks for the suggestion I believe I am doing what you suggested.

Hoo Roo for now Harry

Reply | Quote

Jeremy

To follow our progress go back to the initial thread starting point (a reponse to your suggestion is included there too)

Thanks for the suggestion I believe I am doing what you suggested.

Hoo Roo for now Harry

Reply | Quote

I’d like to take a shot at this…if I’m undermining your intelligence or anything of the sort that is NOT my intention…..

Have you made sure that your input fields in your form are linked to the columns/fields you want to enter the data into? If you’re using separate tables (I.E. a separate table for things such as address, one for Christian names, etc.) and you’re inputting the data in the same form, you need to have some sort of common denominator between them for access to recognize them as belonging to the same person (primary key, social security number, etc)…something different for all. With that primary key, Access can then know to export the info from your form into specific columns throughout your tables.

That’s how I’d start with it…but I consider myself a beginner user and I’m posting not only to try to help, but also for my own knowledge….

Reply | Quote