Solved

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

Posted on 2009-07-01
15
517 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now