User Profile

Qtip23 · Sep 28 '11, 05:21 PM

@NeoPa your assessment is correct. case closed.

Qtip23 · Sep 28 '11, 02:07 PM

@zepphead80 you are right. what I meant was that I took a step back and re-evaluated my whole approach to building this particular Access application.

I used the SQL string as suggested above (without the parameters). Yet incorporated other action queries to manipulate data selected by the user, etc. Still working on this effort as we speak.

Qtip23 · Sep 28 '11, 02:51 AM

@NeoPa I did exactly as you suggested -- breakdown my SQL into smaller parts. What I am learning is the power of action queries.

Instead of focusing on the parameters in SQL string, I have taken them out completely. I have taken another completely different approach.

Having more success by using a combination of the control values and action queries (i.e. insert, append and delete queries) throughout my development....

Qtip23 · Sep 23 '11, 07:43 PM

After further research on Bytes and other sources, looks like there maybe an issue with the DLL's and Access 2007 when using ADO. So I am going to try DAO.

Let me see what happens.

Qtip23

Qtip23 · Sep 23 '11, 07:00 PM

Not having much luck with the parameters. So I am using a variable to store the value of the control.

I have my Immediate Window open and in debug mode on the string. I see the values that I select. However, the SQL string does not run. I am receive a weird error message -- Method "Command" of object 'View" failed. What could be causing this?

Here is the code I use to apply the SQL statement to the stored...

Qtip23 · Sep 22 '11, 04:31 PM

@Rabbit

Thanks for the response. I tried removing just the Parameter statement first. Initially, I received a parameter pop up box when I opened the form. So I continued troubleshooting and tried the other recommendations . This proved to be a good first step.

@Zepphead80

Thanks for your response too. I am using ADO to connect my query results (should not matter though). I noticed that by not using any...

Qtip23 · Aug 19 '11, 05:27 PM

@Rabbit @NeoPa
I found the link discussing the testing/debugging items to be a wealth of information, especially the use of the debug.print throughout my code.

I have been using it to see the values of my variables and form control selections. What I have gathered based on my complex queries is that I need to rethink my table relationships. So for now, I am going back to the drawing board (for a minute).

Tha...

Qtip23 · Aug 19 '11, 02:52 PM

Wow, it's amazing what you can find when you look a little deeper. LOL.

Actually, I found all the properties I needed when I clicked on the subform. I opened the Property Sheet utility of Access and it had all the options I was looking for.

There are Show by Default and Let users view options.
I was able to deselect Field List.

Works like a charm.

Thanks.

Qtip23 · Aug 19 '11, 01:42 PM

@Narender

Hi --

So I think what you are saying is that this feature can only be set through VBA coding, right?

For example, something like: Graph24.Enabled = False

Will try and see what happens.

Thanks,

Qtip23 · Aug 18 '11, 07:41 PM

So while I am not getting any syntax error messages on my SQL string after clicking on button.

Code:

strSQL = "SELECT tbMaster_A.* FROM tbPrograms " & _
             "INNER JOIN tbMaster_A ON tbPrograms.GrPrgID=tbMaster_A.GrID " & _
             "WHERE tbPrograms.[GrID] = " & strID & _
             strCityCondition & "tbMaster_A.[City] " & strCity &

...

Qtip23 · Aug 18 '11, 06:19 PM

Ok, so I think I am getting closer to the answer.

I did forget to mention that this query is based on a one to many relationship.

So, the tbPrograms is on the one side and tbMaster_A is on the many side. They are related by the GrPrgID field which is the PK of tbPrograms and FK of the tbMaster_A table.

I went back to look at the query in Access, so now I have the following:

Code:

PARAMETERS

...

Qtip23 · Aug 18 '11, 05:11 PM

@Rabbit
Yep, I see where you are going. Actually, the and strAreaConditio n and strCityConditio n represents the selection from the option button.

These conditions represent a logical AND or OR.

So let's say we have the following string variable values,

Code:

strID = 2 
strCity = LA 
strCityCondition = AND 
strArea = East
strAreaCondition = AND

The query...

Qtip23 · Aug 12 '11, 01:04 PM

It's working now. I followed some of everyone's tips.

@Rabbit - I did not see the number two when I hovered over. Perhaps this is because I turned off the Break on All Errors.

@Mihail @NeoPa - I ran the query in the database again. It worked like a charm.

I think perhaps the code was getting hung up since the query is based on a form control. The query does not display anything until the parameter (e.g....

Qtip23 · Aug 12 '11, 03:00 AM

Hi Rabbit,

I have the error trapping set to "Break on All Errors". Line 119 is highlighted.

Not quite sure why because the query exists.

Thx.

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Parameter syntax error

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Data mismatch message

Prevent data field movement in subform

Leave a comment:

Leave a comment:

Suddenly receiving Error #3270 - Property Not Found

Automating Pivot Charts

&nbsp is messing up Access Reports