Linking cell information from query result to form problem???

[Leover]

Hello,

First of all Thanks to Hymn and Parkroyal for the reply to my earlier post but I think I wasn't very clear about the whole dilemna that is my database.

To Parkroyal and Hymn thanks and the subform idea, it will come in handy later on for a different application in the database.

The problem I am facing is this,

I have made this search form which seaches my data base for a project number and project type. This search form is linked to a query which seraches the following categories. This works excellent and is really cool.

What I am trying to do now is link the results from the query to there corresponding form. When the results from the query appears, it is in Datasheet view with the different project numbers and projet types that fit the search criteria along with some of the other fields.

When the query table appears I would like to be able to click on the project number and view the details of the project which is in a form.

I am stuck because I don't know how to link the project number in each individual cell to its corresponding form.

I've tried making a macro but there are no commands that can link the cell to the forms? Or is there?? To make it worse my ability to write modules is about as good as India's cricket teams fielding abilities. I'm terrible at writing VB modules in fact any form of code.


If anyone has done anything similar I would like to hear about how you attacked the problem.

Cheers

Leover

PS: Sorry for long email

 

[Blorf]

Quick question. The search form opens a query with criteria set by the form? In other words, the user is just viewing a Query? Not a datasheet Form? If so, change that, and make the user view a Datasheet Form based on the Query. At least that way you can write on Click event Macros for individual fields. I don't beleive you can do so with just a Query.

Once thats done (Assumine it isn't already), I can help you with the VB Code you need. It is actualy pretty short and sweet.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[Leover]

Chaz

From my understanding of MS Access, A button on the search form is controlled by a macro which has in it the command Open Query Command. So In answering your question I quess we are viewing the results obtained by the query, which i guess is a query.

The thing I am a little confuesd with is that, when it says DATASHEET view, isn't that in datasheet form already because it is taking all the project numbers from the tables and placing them onto the query result in datasheet view?

Did that make any sense??

 

[Blorf]

I understand what you are saying with Datasheet view, but Still, it is not a Form. A Form, in Datasheet or otherwise has event trapping options which are not available in a Query, even though they may look the same.

If you just use the form Wizard to make a Form based on that query, And use the Macro on your form to open the new form in instead of opening the query, then you can assign those event traps as required.

So, if we say make the On Double Click event for your field, then when the user double clicks it, we can make it do things, and know the value in the field double clicked, and use that info for our purposes.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[Leover]

Chaz,

Yeah mate I get ya,

Im on to it will get back to you in about 20 minutes ant ell ya how it goes.

Cheers mate.

 

[Leover]

Chaz,

Sorry i didn't get back to you in 20 yesterday our IT guy has the only 1 licencense for Access and its on his computer so I was able to do anything with it yesterday.

Anyways, just done it and works sweet! Thanks,

One last question how do we get the form to show in DATASHEET VIEW instead of form view. I tried changing it thrugh Proporties>DefaultView and Properties Allowed View but it isn't working??

Cheers

 

[Blorf]

You are opening the for with a Macro? If so, easy to change the Macro. The openform option will have form name first, then view. Chang the view to Datasheet.

No problem on the Delay. Glad I was able to help.

ChaZ



Ascii dumb question, get a dumb Ansi

 

[Leover]

Chaz,

Dead set mate your a bloody legend!!!
Now I just have to find someone to the data entry for me and I will be sweet.

Cheers mate

Leover

 

[Blorf]

Data entry indeed. Personally, I hate it.

Glad to help.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[Leover]

Hey Chaz,

I have encountered a problem.

I have made macro which opens a form but I cant get it to go to that specific record? How do we make is so that It goes to that record.

My macro is simple, it has.

Open Form
GoToRecord

It opens the Form but does't got to the record?

Any suggestions.

Cheers

 

[Blorf]

Two ways. One is kind of easy, the other requires a little more familiarity.

Easy way, add a line to your macro called Findrecord

In the first option is Find what, here you refer to the field you clicked on to open this form. Say the form you click on is called "MyForm" and the field you click on is called "MyField", so you would put:

=Forms!MyForm.MyField

Match Whole filed is next, then no for Match Case, then search All. The other stuff is pretty clear.

The other option is to change the data source of your form so it filters out everything except the item you clicked on.

Give the first a try. If you have problems, I will help with the other option. The second option is probably better though, since it will prevent the user from moving around to records not related.

Any way, give it a try.

Your friend,
ChaZ

P.S., any luck with the Data Entry side?


Ascii dumb question, get a dumb Ansi

 

[Leover]

Hey ChaZ,

Tried Adding that line in butstill no result, it just opens up the Form and shows all the results in the database.

My macro looks like this,

Open Form
Find Record
Go To record

In Find Record, for Find What i entered this,

Forms!MPS FORM.Main table 1. Project Number

Which is the form Name I wanna look up and the Field on that form which I wanna match.

The other commands are standard.

What is this second option?

Cheers

Leover

PS: I am trying to convince our team assistant to do the data entry? It's gonna need a lot of work!! Even more work than building this database.

 

[Blorf]

Before we try the second option, change something for me:

Change
Forms!MPS FORM.Main table 1. Project Number

to

=Forms![MPS FORM.Main table 1].Project Number

I am assuming that MPS FORM.Main table 1 is the name of the form that has the datasheet records that you are clicking to open the new form yes? If so, access doesn't like the spaces in the names, so you gotta surrond them with the brackets [], and you need the = sign also.

Give that one last try, then we go from there.

ChaZ


Ascii dumb question, get a dumb Ansi

 

[Leover]

Hey ChaZ,

The name of the form that I am clicking which has the datasheet records to open the new form is RESULTS FROM QUERY. The MPS FORM is the name of the form I want to open up from the RESULTS QUERY FORM and Main table. Project Number is the field name in the MPS FORM.

The Macro Now Looks Like this

Open Form
GotoRecord
FindRecord

In Open form I have
Form Name>>>>MPS FORM (which is the form I want to open.)

GotoRecord I have
Object Type>>>>>Form
Object Name>>>>>MPS FORM

FindRecord I have
Find What>>=[Forms]![MPS FORM].[Main table 1.Project Number]
Match>>>>>>Whole Field
Match case>NO

I also tried
Find What>>=[Forms]![RESULTS FROM QUERY].[Project Number] (where project number is the name of the field on the datasheet I am clicking on)

Hopefully that helps

 

[Blorf]

is Projectnumber also the name of the field in RESULTS QUERY FORM form? Remember that the find what field wants the value to Find, not the field name to look in, so if you click on the field to launch the new form, and that field has the value you want found, then the Find what references that.

Like =forms![RESULTS QUERY FORM].[Project Number]

Note that because there is a space in Project Number, we have to surround it in the brackets.

Now assume for a second that this doesn't work. What is the data source for the form MPS FORM? If it is a table, then we can change it to a query. The query will look something like this:

Select Field1, Field2, Field3, blah blah from TableName where Field1=forms![RESULTS QUERY FORM].[Project Number]

That is the second option, should all this not work.

Also, if you want, you can e-mail me the Access file and I can take a look.

Lexicon_One@Hotmail.com

ChaZ

Ascii dumb question, get a dumb Ansi

 

[Leover]

HI CHAZ,

Yes, [Project Number] is the field name in the RESULTS QUERY FORM and the source for the MPS FORM is a massive query.

I have emailed you a copy of the db, have a look and see what I am trying to do, I think you will understand it better.

Cheers

Leover

PS: Jess our Team assistant argreed to do the data entry, only cost me a medium fruit salad from the take away store.

 

[Blorf]

Hi Leover, got your e-mail. I think I got it sorted out.

I am not sure why the Macro didn't work out, but I solved it by changing the data source of the form you are launching to this

SELECT [Main table 1].*, [Main Table 2].*, [Main table 1].[Project Number] AS Filter
FROM [Main table 1] INNER JOIN [Main Table 2] ON [Main table 1].[Project Number] = [Main Table 2].[Project Number]
WHERE ((([Main table 1].[Project Number])=[Forms]![RESULTS FROM QUERY]![Project Number]));

You had it calling for MPS Query. The above is the same as that, but with the filter built in. This filter will prevent the user from moving from one record to another that does not match.

You can just cut and past that into the data dource property of the form. I will e-mail it back to you also.

Your friend,
ChaZ

P.S. Congrats on the low price of a Medium Fruit Salad!


Ascii dumb question, get a dumb Ansi

 

[Leover]

ChaZ,

mate its awesome, yeah I don't know why it didn't work that other way either.

Anyways mate, I owe you a VB (beer that is - Victoria Bitter). If your in around the North Shore of Sydney i'll buy ya one

Cheers

Leover

 

[Blorf]

I look forward to it. I keep your name on file incase I get sent that way for business or something. In the mean time, I think I will go enjoy a local Macro brew and relax for the evening.

BTW, I thought the form that gets launched by the double click is rather cool looking. May copy the design if you don't mind.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[Leover]

Go for your LIFE!!!

Cheers