Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Can't access records in ADP

Posted on 2004-08-16
9
Medium Priority
?
472 Views
Last Modified: 2013-12-05
Hi all,
    I have attempted to transfer my MDB into an ADP running on SQL Server. In the process, I managed to get all the data working after a while but my forms are not working properly. I have a form that contains multiple subforms. Form A (my primary form) has subforms (Form B, C, etc.). The table for Form A is Table A. For Form B, it is Table B, and so on. The tables are connected by a 1-to-1 or a 1-to-many relationship. They all reference Table A's primary key for the relationship.
    I am holding information of people in these tables, so Table A has their personal information such as address, etc. Table B has property they own, etc. Table C may have answers from a survey they have filled out. However, a person in Table A may not have a record in Table B because they do not have any property. Additionally, they may not have filled out a survey, so they do not have a record in Table C.
    Now my problem presents itself. All the relationships are set up nicely, etc. If you open up the form and scroll through the records, they all have the correct information in the subform. However, if you come to a record without any record for Table B or C, you get an empty subform. You cannot create a new record either. All the recordset navigation buttons are greyed out. However, on some of the subforms you can see the subforms and the navigation buttons are not greyed out. Can someone please tell me why? I know that in Access, the next record is "created" for you if you have no information and SQL Server does not do that. Is this the problem? How do I solve it? Thanks so much guys.
                   Sincerely,
                         JL
0
Comment
Question by:jl_forum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 11816987
after upsizing you must make sure every table has a primary key defined...this is usually the cause of not being able to add records

have a look at your tables and define one if it doesn't exist.
0
 

Author Comment

by:jl_forum
ID: 11823589
Hi Sid,
    I did do that and it seems as if problems seem to exist regardless of whether a key is there. This problem really only occurs on two subforms. I don't quite understand why. One of these subforms only shows the contents of the subform if they have a record. The other subform doesn't show anything regardless if they have a record or not. Any ideas?
            Sincerely,
                  Jon
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 11832306
Can you open the subform in regular form view (as opposed to opening it as a subform in the main form) and see records?  Have you tried to rebuild the form?  Do you use any third-party controls, or graphics controls?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 36

Expert Comment

by:SidFishes
ID: 11832427
had a thought...do you have your pk's set up to be an INT with autoincrement enabled?...or are you creating them with code

this might make a difference?...
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11835687
Try: Properties of SubForm - Data Tab - Enabled: Yes, Locked: No
0
 

Author Comment

by:jl_forum
ID: 11836805
Hi guys,
   Thanks for all the comments! I tried opening the subform in regular view and its okay. I didn't rebuild the form but then again, I don't think its a problem with the form. No third-party controls are involved. This is a simple Access-driven subform (well... at least this part of it is). The primary keys are created automatically. No code is involved here. The pk's used to be autonumbering (random because of synchronization), but since it is now SQL Server, it autoincrements as far as I know. As for the subform tab data, it is enabled and everything seems good to go.
    One thing that I failed to mention that is may be of some use. When I open the main form, I get the error "Syntax error or access violation". However, the form opens up afterwards. Functionality-wise, there doesn't seem to be too much of a difference. It functions like it did before with the exceptions of the problem I said. Looking at routinet's comment, I tried to place an additional subform on the main subform (with the problem subform being the control source). I got the same blankness, however, I also got this error upon trying to just add a subform to the form: "Your code contains a syntax error, or a Microsoft Access function you need isn't available". I assumed this is the same error it is referring to when I try to open the form. Now, when I open the form, I get the "Syntax error or access violation" and I also get "Line 1: Incorrect syntax near ';'.". The subform does not have any VBA code either... what gives?? Please help!!
                 Sincerely,
                         JL
0
 

Author Comment

by:jl_forum
ID: 11837039
Hey guys, I found something. I changed the control source of that subform to something else and I seemed to fix it. Before it was a query to fill in a label while opening it. I think that solved it, as I can see it now... as for the other subform... I can see it but no records show up still.... Thanks for your help!
              JL
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12665034
Closed, 125 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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