linking between forms not workin 1

[ralphus]

i used the access wizard to create a button to link from one form and open another form on a specific record or records and it is not working.
it is opening the form but it seems to be opening it and showing a new record what is the probem and how do i solve it?
thanks!!! whoever you shall be

 

[Fekri]

Hi,

Can you explain a little more..

On onclick event of you botton whzt is written??
can you paste that here to see what's the problem??

Ali

 

[cghoga]

If it's opening to a New Record, it could be that the Data Entry property of that form is set to Yes.

Also, you might look at filtering.

Hope this helps.

 

[ralphus]

Here is the code for my link to another form, and the data entry propert on the form i am linking to is set to no

Private Sub products_Click()
On Error GoTo Err_products_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stFormName As String

stDocName = "Product_Table"
stFormName = "Site_Table"

stLinkCriteria = "[Site_Name]=" & Me![Site_Name]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, stFormName, acSaveNo

Exit_products_Click:
Exit Sub

Err_products_Click:
MsgBox Err.Description
Resume Exit_products_Click

End Sub

 

[TheAceMan1]

How are ya ralphus . . . . .

From the code you've posted, I believe the [purple]criteria is not returning any records[/purple] (hence [blue]new record only shows[/blue]).
[ol][li]The code shows [purple]Product_Table[/purple] as the form name. Is this correct?[/li]
[li]By any chance is [purple]Site_Name[/purple] on a [blue]subform[/blue] while the [purple]button[/purple] in on [blue]mainform[/blue] or vice versa?[/li][/ol]

See Ya! . . . . . .

 

[ralphus]

no this code is from a button on a form called site_name the button is supposed ot open another form called product_table with a record of the same site name
the idea is u can see what that site has bought

 

[TheAceMan1]

[blue]. . . code is from a button on a form called site_name . . .[/blue]

I'm not sure if your saying the form or the button is called [purple]site_name[/purple]. Either way you show the formname as [purple]Site_Table[/purple] and the button name as [purple]products[/purple].

In the meantime try this:

[blue][purple]Change:[/purple]
stLinkCriteria = "[Site_Name]=" & Me![Site_Name]
[purple]To:[/purple]
stLinkCriteria = "[Site_Name]='" & Me![Site_Name] & "'"[/blue]

See Ya! . . . . . .

 

[ralphus]

The starting form is called site_name a button on this form called products opens another form called proucts. I want it to open the form products and open on all records with the same sitename or the first record witht that sitename.

also aceman that code u gave me doesn't work it comes up witht the error open form canceled

 

[TheAceMan1]

[blue]The starting form is called site_name[/blue]

In you code you have:

[blue]   Dim [b]stFormName[/b] As String
   [b]stFormName[/b] = "[purple][b]Site_Table[/b][/purple]"
   DoCmd.Close acForm, [b]stFormName[/b], acSaveNo[/blue]

According to you the assignment should be:
[blue]stFormName[/blue] = "[purple]Site_Name[/purple]"

Now . . .

[blue]the button is supposed ot open another form called [purple]product_table[/purple] . . .

[purple]then in your last post:[/purple]

a button on this form called products opens another form called [purple]proucts[/purple] . . .[/blue]

Either way, make sure [purple]stDocName = "Product_Table"
[/purple] is the right name for the [blue]opened form![/blue]

Finally in [purple] stLinkCriteria = "[Site_Name]=" & Me![Site_Name][/purple] the criteria is pinging on the field [purple][Site_Name][/purple]. Are you saying you have a field in both forms with this name . . . or is it something else?

See Ya! . . . . . .

 

[ralphus]

i am using the right code, and the names for the forms are correct but for some reason it returns the error message
sysntax error (missing operator) in query expression '[site_name]=TAVERN FAYRE'.
the tavern fayre bit is the name it is taking from the site database to look up products (that the site has bought) from the product database.
why will it not link is it because i have the same name or what??????
please help its doing my head in now???

 

[OceanBlue]

I worked on something very smiliar to this and I had that problem but then resolved it. What is the primary key in the site_name form? And do you have that linked to the foreign key in the products form (1 to Many relationship)

 

[ralphus]

the reationship is one to many yes but thats not what it is complaining about it is saying that the expression is wrong, i used the wizard and it doesn't like it its reay annoying gggggrrrrrrrr

 

[OceanBlue]

What is the name of the primary key field? I can show you what I did with mine that made it work.

 

[ralphus]

for the site_table the primary key is site_id which is an auto number, for the form i wish to open on a specific record it is called product_table and the primary key for that is product_id which is an autonumber

 

[TheAceMan1]

the tavern fayre bit is the name it is taking [purple]from the site database[/purple] to look up products (that the site has bought) [purple]from the product database[/purple].
[blue]why will it not link is it because i have the same name or what??????[/blue]

[purple]Unless this is explained, it appears we've all been hunting in the dark![/purple]

See Ya! . . . . . .

 

[formerTexan]

Hi,

Since you seemed to have narrowed down the problem to a sysntax problem in a query expression, lets have a go at that.

If the query expression problem seems to be with the recordset query for the called (form you are opening) form, try opening this query in datasheet view and see what it returns. You should get a full recordset. If not, then post the SQL statement and let us have a look.

Cheers,
Bill

 

[willir]

ralphus

You have a lot of talent trying to help you on this post.

(Congrats AceMan on TipMaster of the Week -- Awesome!)

We know what you are trying to do - can be done.

I suspect the problem is that those trying to provide support are lacking specific details.

To achieve the objective, you need to...
- open the form
- provide a valid link criteria
- a valid criteria is
-- a) found on the current / source form as control field variable, for example, a text box,
-- b) is also used on the target form
-- c) the data types have to match on the source and target. If you use a long numeric integer on the source, the target field uses the same
-- d) the syntax has to be correct. Text strings have to encaspulated with " quotes, date fields have to be encapsulated with # octophorps.

A typical example of coding is...

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmEmployeeMaster
    
    stLinkCriteria = "[EmpID]=" & Me.EmpID
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Now looking at your code with comments in green...

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stFormName As String[COLOR=green]
'Why are you using stFormName in addition to stDocName?
[/color]

    stDocName = "Product_Table"[COLOR=green]
'Unusual to have a form named Product_Table except
'Access will default to the name of the table when
'creating a form using the wizard -- in future
'consider changing the name to something like [b]frmProduct[/b]
[/color]
    stFormName = "Site_Table"[COLOR=green]
'Man am I puzzled what your are trying to do.
[/color]
    stLinkCriteria = "[Site_Name]=" & Me![Site_Name] [COLOR=green]
'This looks problematic.  
'1) [Site_Name] is most likely a text string which means
'   that you need to encapsulated the text string within quotes
'2) Me![Site_Name] is valid, but if you use Me.Site_Name
'   Access will allow you to pick the variable name from a
'   pick list
[/color]
    DoCmd.OpenForm stDocName, , , stLinkCriteria [COLOR=green]
'This looks fine
'Since you open the form with a "new record", 
'- stDocName is correct
'- stLinkCriteria is not correct[/color]

    DoCmd.Close acForm, stFormName, acSaveNo [COLOR=green]
'What is the prupose of this?
'Are you closing one form after loading the other?[/color]

I think the following will work for you with comments prefaced with a single quote ' ...

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim strQ as String

    strQ = Chr$(34)            'quote character, my way
    stDocName = "Product_Table"
    
'   Get site name from form and encapsulate with quotes
    stLinkCriteria = "[Site_Name]=" & strQ & Me.Site_Name & strQ

'   Open target form with link criteria
    DoCmd.OpenForm stDocName, , , stLinkCriteria

'   Close current form using Me.Name reference
    DoCmd.Close acForm, Me.Name, acSaveNo

Richard

 

[ralphus]

I am appreciating all the help i am getting guys and thanks allot.
Willir i used the code you gave me and i put it within the on click even of the button in question theseare the errors i received

and this is the part of the code it is refering to have i pasted it wrong or something???

Mucho Thanko everyoneo

 

[willir]

Okay

I tested the code provided -- seemed to work just fine.

I created a form called "Product_Table"
I had a valid record on the exiting form.
When I opened the Product_Table, the form opened and selected record on the source form was found and displayed.

I researched your run-time error:
Microsoft web site:
SendObject method fails in Access 2000
Instead of a SendObject, your issue the OpenForm.
There recommendation, for Access 2000 is to install the service packs (I think A2K / Office 2k is up to SP4)

ODE97: Filter By Form Not Available in Run-Time Applications

In Microsoft Access 7.0, Filter-by-Form works in both the full version and the run-time version. However, the Filter-by-Form functionality wasn't designed to be available in a Microsoft Access 7.0 run-time application.

Microsoft recommends downloading a demo database to

The Qbf.exe file contains a database created by Ken Getz that demonstrates how to create a custom Filter-by-Form, which you can use in any application, including a run-time application.

ACC97: Filter By Form Example for Run-Time Applications Available in the Download Center

I think you may want to consider the version of Access you are running. Update to the current service pack. And review the demo database by Ken Getz if you have to use an older version of Access.

Richard