Solved

Microsoft Access 2007 form - Everything visible in design view but invisible in Form view

Posted on 2009-07-01
15
522 Views
Last Modified: 2012-05-07
Hi,
I am reletivley new to ms access and have a problem with a form.

We exported the form in question from a known working access db and imported to the db in question which has similar properties.

The problem is that no data appears when looking at 'form view' - 'Design view is fine and all the fields/tables exist as expected.

Not sure where we are going wrong but any help much appreciated?

Database1.mdb
0
Comment
Question by:Ripla5
  • 8
  • 7
15 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24753298
The form gats its data from a query involving two tables.
Do you have the same tables in your new database? (They are not present in the sample you posted).
0
 

Author Comment

by:Ripla5
ID: 24753367

The export is from the main (new) database.
I have attached the export from the known working database and cannot see any differences? - Except the results show in this one when opened from within the actual db itself
Database2.mdb
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24753446
The form is used to display  data from two table, called Events and Rehearsals.
It is configured so that its only function is to display data.  Data cannot be edited, entered or deleted through this form.
If there is no data to display then that's it.

The data to de displayed is for event types CO and SH and the event date cannot be in the past.
Also there has to be matching records in both tables (matched on EventID)  otherwise no data for that event  will be displayed.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ripla5
ID: 24753745

So basically the form does not display data as there is no data to display?

I know that the data is in the new database so is it a case of 're-mapping' the form to find the correct data? If so pointers on this would be very welcome!
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24754429
"is it a case of 're-mapping' "

Well yes, in principle.  But how involved this will be is anyone's guess.

As I've said, the data source for the form is a query based on two tables, Events and Rehearsals.
Do you have two such tables in your database?
If so then you should open the form in design view and display its properties.
In the recordsource property is the sql code of the query.  Click into this cell and then click the Build button (...) to the right.
Access will try to display the query in the query grid.  Where it finds matching fields it will show the field name and the table name in the column.
Where it can't find a match it will show the field name with prefix like..'Expr1'  and no table name.
You need to find the correct field for each of these. You might also get an error message saying that relationship cannot be created - this will be due to the field names used to link the tables not being present in the new tables
0
 

Author Comment

by:Ripla5
ID: 24755508
I think the events and rehearsals are in the database yes.

I opened the forms in design view, managed to fit them side by side and take a screen dump, the working db is to the right of the image while the 'non-working' is to the left. After clicking the (...) I get the attached.

I can't see any prefixes like you mention - Am I looking in the right place
db1.jpg
0
 

Author Comment

by:Ripla5
ID: 24755535
Also a screen dump of before clicking the (...) if thats any use?...


db.jpg
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24755750
The values you show relate to the highlighted textbox, not to the form.
To show the form properties, double click the box to the left of the horizontal ruler.
0
 

Author Comment

by:Ripla5
ID: 24756197
I think I got it, working and non working attached.....

There are some obvious differences in the non working which are highlighted in red but I still can't see anything which stands out as the casue of the problem.....


working.jpg
non-working.jpg
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24756464
I suggest that you do this..
In the 'non-working' version find the query view again, and this time double click the 3 missing fields to add them in to the grid.

Then click the SAVE button and save the query with a name of say qryEventData.

close the query- say Yes when asked if you want to update the property.

Close the form.
Now open the saved query and see if you get any records in the results.

If you don't -i supect you won't - then that means there is no data which satisfies the criteria and you will have to start looking at that.
0
 

Author Comment

by:Ripla5
ID: 24756588
No joy I'm afraid and I'm probably already well out of my depth here so looking at data which essentially doesn't exist makes me even more nervous - Is this something which is relatively straight forward?

Sad thing is I'm ok with SQL in relation to MYSQL and PHP, I'm assuming access works on a similar format its just understanding where everything is and how to piece it together!
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24756970
I suggest you create a simple query to see what is going on.
Start a new query in design view and select the two tables.  The join should happen automatically.  Then add the eventid , event name, eventdate and eventexpdate from the event table and the rehearsalid and rehearsal night from the rehearsal table just by doubleclicking in the field lists.
See what you get.  

If there is still nothing in the results then you have some data issues;  it would mean that there are no records with matching eventid values.
0
 

Author Comment

by:Ripla5
ID: 24757833
Hi Peter, your patience is inspiring!

Ok, I managed to do everything as you suggested - hey presto we have data results after clicking 'run'.

Still not clear what this means but by the sounds of it we have a starting point!
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 24758499
Ok - add the event type to this query and also sort on the eventtype.
You should be able to see if there are any eventtype values which are co or sh, which is what the form is looking for.
0
 

Author Comment

by:Ripla5
ID: 24758947
Looks like we are getting somewhere now.

Understood where you are coming from and one-by-one I have run the query each time, I found that the datafailure occured when I added the filter '>Date()' to the 'EventExpDate' query.
I don't actually need this as I can sort by ascending, but for now it looks like the penny has well and trully dropped - I now understand that I need to understand each and every data field > what it relates to, and that a query has to be created before a form can be viewed - I've learned more about access in one day simply with your help and a bit of luck!

Thank you for your help!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question