473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

opening specific records in forms ?

Help,

When I am finished entering in data into my form A, I press the save button
that
saves this new data to a new record. However I would like my other form to
open at a specific record, the record that I just finished working on.

Ideas?
Nov 12 '05 #1
9 4729
Use the WhereCondition of the OpenForm action.

Assuming a primary key named "ID", that would be:
DoCmd.OpenForm "MyOtherForm", WhereCondition:="ID = " & Me.ID

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BLUE WATER" <no**********@nowhere.com> wrote in message
news:NT******************@news04.bloor.is.net.cabl e.rogers.com...

When I am finished entering in data into my form A, I press the save button that
saves this new data to a new record. However I would like my other form to open at a specific record, the record that I just finished working on.

Nov 12 '05 #2
BLUE WATER wrote:
Help,

When I am finished entering in data into my form A, I press the save button
that
saves this new data to a new record. However I would like my other form to
open at a specific record, the record that I just finished working on.

Ideas?

Expanding on Allen Brown's reply...if the form was already open you
could set the filter or find the record.

'setting the filter
Docmd.OpenForm "MyForm"
Forms!MyForm.Form.Filter = "ID = " & Me.ID
Forms!MyForm.Form.FilterOn = True
'finding the record
Dim rst As DAO.Recordset
Docmd.OpenForm "MyForm"
set rst = Forms!MyForm.Form.Recordsetclone
rst.FindFirst "ID = " & Me.ID
Forms!MyForm.Form.BookMark = rst.Bookmark
set rst = Nothing

Allen's method is much simpler but you might find these methods useful
in other situations when working with Access.

Nov 12 '05 #3
Hi Allen,

In my table view, the primary unique identifyer is a variable known as
"Company_ID".

When I replace your "ID" with "Company_ID", the program crashed saying:

Compile error:
Method of data member not found

****
What am I doing wrong here ? I put this code following the
Private Sub commandsaveandentry_click()

Thanks.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...
Use the WhereCondition of the OpenForm action.

Assuming a primary key named "ID", that would be:
DoCmd.OpenForm "MyOtherForm", WhereCondition:="ID = " & Me.ID

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BLUE WATER" <no**********@nowhere.com> wrote in message
news:NT******************@news04.bloor.is.net.cabl e.rogers.com...

When I am finished entering in data into my form A, I press the save

button
that
saves this new data to a new record. However I would like my other form

to
open at a specific record, the record that I just finished working on.


Nov 12 '05 #4
Allen, or others,

In my tables there is a unique key that identifies each new company record
with a number, this
variable is Company_ID as has been typed up in the edit table data view.
Thus I think you are
referring to this unique new identification number that is created from my
AddNewCompany
form, thus that can be used to open the new form after pressing the button
(known as the
Commandsaveandproductentry button). The new form is called
Input_Chemicals_Form as
viewed from the Access2002 forms list.

Therefore what exactly do I need to fill in the "xxxxxxx" with ? please let
me know exactly to the
letter so I don't get bugs again, I must be missing something. I will
remove the ' in the original
VBA code.

Here is my code for the button that is pressed section in my "AddNewCompany"
form:

Private Sub Commandsaveandproductentry_Click()
If Len(Me.Company_Legal_Name & vbNullString) > 0 Then
Call AddCompanyInfo
DoCmd.Close acForm, Me.Name

'DoCmd.OpenForm "Input_Chemicals_Form", xxxxxxxx

Else
MsgBox "The Company Name field can not be left blank.", vbCritical, "No
Company Name Error"
Me.Company_Legal_Name.SetFocus
End If
End Sub


****
Thanks a million.
Nov 12 '05 #5
BLUE WATER wrote:
Allen, or others,

In my tables there is a unique key that identifies each new company record
with a number, this
variable is Company_ID as has been typed up in the edit table data view.
Thus I think you are
referring to this unique new identification number that is created from my
AddNewCompany
form, thus that can be used to open the new form after pressing the button
(known as the
Commandsaveandproductentry button). The new form is called
Input_Chemicals_Form as
viewed from the Access2002 forms list.

Therefore what exactly do I need to fill in the "xxxxxxx" with ? please let
me know exactly to the
letter so I don't get bugs again, I must be missing something. I will
remove the ' in the original
VBA code.

Here is my code for the button that is pressed section in my "AddNewCompany"
form:

Private Sub Commandsaveandproductentry_Click()
If Len(Me.Company_Legal_Name & vbNullString) > 0 Then
Call AddCompanyInfo
DoCmd.Close acForm, Me.Name

'DoCmd.OpenForm "Input_Chemicals_Form", xxxxxxxx

Else
MsgBox "The Company Name field can not be left blank.", vbCritical, "No
Company Name Error"
Me.Company_Legal_Name.SetFocus
End If
End Sub


One thing you do not show is AddCompanyInfo code. Does it add a record
to the Company file?

There is the field CompanyID. Do you want to open the form
InputChemicalsForm to that company ID?
DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID
will open the form to the chemicals of that company id.

If you want to add a record, you could enter
Docmd.Openform "Input_Chemicals_Form",,,,acFormAdd
Forms!Input_Chemicals_Form!CompanyID = Me.CompanyID
You could even pass the name with
Forms!Input_Chemicals_Form!CompanyName = Me.CompanyName
Or when you open Input_Chemicals_Form you can gram data from the current
form. In your OnOpen event for Input_Chemicals_Form you could enter
Me.CompanyName = Forms!YourCurrentForm!CompanyName

THere are multiple ways of doing what you want to do.

Nov 12 '05 #6

"Salad" <oi*@vinegar.com> wrote in message
news:TH*****************@newsread1.news.pas.earthl ink.net...
BLUE WATER wrote:
Allen, or others,

In my tables there is a unique key that identifies each new company record with a number, this
variable is Company_ID as has been typed up in the edit table data view.
Thus I think you are
referring to this unique new identification number that is created from my AddNewCompany
form, thus that can be used to open the new form after pressing the button (known as the
Commandsaveandproductentry button). The new form is called
Input_Chemicals_Form as
viewed from the Access2002 forms list.

Therefore what exactly do I need to fill in the "xxxxxxx" with ? please let me know exactly to the
letter so I don't get bugs again, I must be missing something. I will
remove the ' in the original
VBA code.

Here is my code for the button that is pressed section in my "AddNewCompany" form:

Private Sub Commandsaveandproductentry_Click()
If Len(Me.Company_Legal_Name & vbNullString) > 0 Then
Call AddCompanyInfo
DoCmd.Close acForm, Me.Name

'DoCmd.OpenForm "Input_Chemicals_Form", xxxxxxxx

Else
MsgBox "The Company Name field can not be left blank.", vbCritical, "No Company Name Error"
Me.Company_Legal_Name.SetFocus
End If
End Sub
One thing you do not show is AddCompanyInfo code. Does it add a record
to the Company file?


Yes, it adds a record (new record) to the company files.
There is the field CompanyID. Do you want to open the form
InputChemicalsForm to that company ID?
The field is actually Company_ID and not CompanyID. Although the name of
the
feild in the properties is Company ID (notice the space between ID and
Company),
but the variable name is Company_ID.

Yes, I want the InputChemicalsForm to open, to open at the record number
that
corresponds the last used forms Company_ID, so that I don't have to search
every
time I decide to go from this form to the InputChemicalsForm. It should
open at
the record of the company I was using (when I exit the form I just finished
using).
DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID
will open the form to the chemicals of that company id.
Is this correct ? should it be Company ID or Company_ID ?
If you want to add a record, you could enter
Docmd.Openform "Input_Chemicals_Form",,,,acFormAdd
Forms!Input_Chemicals_Form!CompanyID = Me.CompanyID
I don't want to add records, since it works already and adds a record by
calling a
function, so i don't think the above is needed is it ?
You could even pass the name with
Forms!Input_Chemicals_Form!CompanyName = Me.CompanyName
What do you mean by pass the name ? from the added record ?or ?


Or when you open Input_Chemicals_Form you can gram data from the current
form. In your OnOpen event for Input_Chemicals_Form you could enter
Me.CompanyName = Forms!YourCurrentForm!CompanyName

I think if I grab the data from my previously used form it may create a
problem, I want
to keep things very simple for future development.
Let me know the answers to the above questions.

Thanks
Nov 12 '05 #7
DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID

I tried to replace the line and my access crashes stating method or member
not found.
It does the same thing if I use Company_ID.

What is the problem here ? Do I have to close the form after this code or
something?

Thanks
"Salad" <oi*@vinegar.com> wrote in message
news:TH*****************@newsread1.news.pas.earthl ink.net...
BLUE WATER wrote:
Allen, or others,

In my tables there is a unique key that identifies each new company record with a number, this
variable is Company_ID as has been typed up in the edit table data view.
Thus I think you are
referring to this unique new identification number that is created from my AddNewCompany
form, thus that can be used to open the new form after pressing the button (known as the
Commandsaveandproductentry button). The new form is called
Input_Chemicals_Form as
viewed from the Access2002 forms list.

Therefore what exactly do I need to fill in the "xxxxxxx" with ? please let me know exactly to the
letter so I don't get bugs again, I must be missing something. I will
remove the ' in the original
VBA code.

Here is my code for the button that is pressed section in my "AddNewCompany" form:

Private Sub Commandsaveandproductentry_Click()
If Len(Me.Company_Legal_Name & vbNullString) > 0 Then
Call AddCompanyInfo
DoCmd.Close acForm, Me.Name

'DoCmd.OpenForm "Input_Chemicals_Form", xxxxxxxx

Else
MsgBox "The Company Name field can not be left blank.", vbCritical, "No Company Name Error"
Me.Company_Legal_Name.SetFocus
End If
End Sub


One thing you do not show is AddCompanyInfo code. Does it add a record
to the Company file?

There is the field CompanyID. Do you want to open the form
InputChemicalsForm to that company ID?
DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID
will open the form to the chemicals of that company id.

If you want to add a record, you could enter
Docmd.Openform "Input_Chemicals_Form",,,,acFormAdd
Forms!Input_Chemicals_Form!CompanyID = Me.CompanyID
You could even pass the name with
Forms!Input_Chemicals_Form!CompanyName = Me.CompanyName
Or when you open Input_Chemicals_Form you can gram data from the current
form. In your OnOpen event for Input_Chemicals_Form you could enter
Me.CompanyName = Forms!YourCurrentForm!CompanyName

THere are multiple ways of doing what you want to do.

Nov 12 '05 #8
On Sat, 10 Apr 2004 22:55:24 +0200, BLUE WATER wrote
(in message <0b*****************@news04.bloor.is.net.cable.rog ers.com>):
DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID
First of all: I would use NamedArguments, i.e.:

DOcmd.Openform FormName:="Input_Chemicals_Form", _
WhereCondition:="[CompanyID] = " & Me.companyID
I tried to replace the line and my access crashes stating method or member
not found. It does the same thing if I use Company_ID.

What is the problem here ? Do I have to close the form after this code or
something?


The main error IMHO is that you close your form and then refer
to a form-control or field in the recordsource, but that information
is gone after a close...
First Open the form and then close the calling form.

Anyway a bit more background:

You don't provide enough information to solve your problem.
You really have to your table structure and relation between the tables
and the recordsources you use for your forms (and the function you use
for adding new Companies).

Anyway, I assume you have a one to many relship between your companies
and products.

For your intention the product form should have the company PK (and other
fields as desired) in the recordsource and the product fields should be
in the recordsource of a subform on that same form.
Link the subform to the main form on the FK<->PK.

Now: What does your Company_save function do? Insert via SQL/Recordset or
just save the Form data (with me.Dirty = False)?
If your inserting a new company and then want to open the product form
you have to be sure to provide the correct PK (CompanyID, FK in the Product-
table) to the Product form.

In the simplest form do a:
If me.dirty then
me.dirty = false
End If

before you open the product form.
Then in the Product-Form-opening code provide the PK of the company in the
WhereCondition-Argument.

OK, pseudo code:

Private Sub YourProductOpenButton_Click()
If Me.Dirty Then
Me.dirty = false
End If
DoCmd.OpenForm FormName:="ProductFormName", _
WhereCondition:= "CompanyPKfield = " & Me.CompanyPKField
(or: WhereCondition:="CompanyPKfield = " & Me.FormControlThatHoldsCompanyPK
'And now you can close the Company Form...
End Sub

CompanyPKfield is the field-name of the CompanyPK-Field in the Recordsource
of the Products-Form Recordsource (main form not subform).
This way access will automatically insert the right FK value into the
products table when entering new Products for a company.

There are a zillion other ways to realize such a setup with a main
and a detail table.

Michael

Nov 12 '05 #9
BLUE WATER wrote:
One thing you do not show is AddCompanyInfo code. Does it add a record
to the Company file?

Yes, it adds a record (new record) to the company files.
There is the field CompanyID. Do you want to open the form
InputChemicalsForm to that company ID?

The field is actually Company_ID and not CompanyID. Although the name of
the
feild in the properties is Company ID (notice the space between ID and
Company),
but the variable name is Company_ID.


In a table, you have a field.
In a form (if it is bound to a table) you have
ControlSource (table field). See data tab
and
Name (how you reference it in code). See other tab

I would like you do do something. Click on queries tab, click
New/Design View. Select any table, click Add, then close. Drag the
field down. Let's pretend it is called Date_Field. I could enter
Happy Days Are Here Again : Date_Field
Substitute Date_field with the field name. Now press View/SQL. If you
look at the SQL code it will be something like
Select Date_Field As [Happy Days Are Here Again]....

You will notice that Happy Days Are Here Again are surrounded in
brackets. If you create table fields with spaces, and you reference the
field via a recordset, you need to surround the field name in brackets.
If you have a space in a control's name on a form, you reference it
the same. Ex: Me.[Happy Days Are Here Again]

So maybe your where filter when opening should be
[Company Name] = '" & Me.Company_Name & "'"
or
[Company ID] = " & Me.Company_ID

We that are trying to help you may use CompanyID or Company_ID or
Company ID...it is up to you to experiment a bit and modify our code to
meet your needs.

Yes, I want the InputChemicalsForm to open, to open at the record number
that
corresponds the last used forms Company_ID, so that I don't have to search
every
time I decide to go from this form to the InputChemicalsForm. It should
open at
the record of the company I was using (when I exit the form I just finished
using).

DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID
will open the form to the chemicals of that company id.

Is this correct ? should it be Company ID or Company_ID ?


In the form, check the NAME field in the OTHER tab of the property sheet.
If you want to add a record, you could enter
Docmd.Openform "Input_Chemicals_Form",,,,acFormAdd
Forms!Input_Chemicals_Form!CompanyID = Me.CompanyID

I don't want to add records, since it works already and adds a record by
calling a
function, so i don't think the above is needed is it ?


No. But someday you might want to re-read this. You have been given a
good lesson. Lot's of good stuff has been given to you.
You could even pass the name with
Forms!Input_Chemicals_Form!CompanyName = Me.CompanyName

What do you mean by pass the name ? from the added record ?or ?


Sure. I am in a form. I call a new form. Before I close the form I am
in I can pass the newly opened form information from the current form.

It would be worthwhile to learn about the Form object module if you want
to program in Access and manipulate forms.
Or when you open Input_Chemicals_Form you can gram data from the current
form. In your OnOpen event for Input_Chemicals_Form you could enter
Me.CompanyName = Forms!YourCurrentForm!CompanyName


I think if I grab the data from my previously used form it may create a
problem, I want
to keep things very simple for future development.


You should do what makes you comfortable.

Let me know the answers to the above questions.


I'm hoping I did.
Thanks


Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: BLUE WATER | last post by:
Help, When I am finished entering in data into my form A, I press the save button that saves this new data to a new record. However I would like my other form to open at a specific record, the...
0
by: BLUE WATER | last post by:
Allen, or others, In my tables there is a unique key that identifies each new company record with a number, this variable is Company_ID as has been typed up in the edit table data view. Thus I...
2
by: Lyn | last post by:
Hi, I am working on a genealogy project in which I have two tables: Person -- one record for each person in the family. Each record has a unique Autonum field (IDPerson). Partnerships -- one...
1
by: Ian | last post by:
I want to open a form at a particular record, but I think I'm running into problems because the recordsource query is executing asynchronously. In the form's open event I use...
6
by: RC | last post by:
My code below will loop through all the records in the table, and when the if statement is true it goes to the ***Me.ContainerNumberProductsTable = GETContainerNumber.Value*** bit like should but...
2
by: Prakash | last post by:
I have 2 tables ... Customer_Master: Cust-Code, Cust_Name Customer_Transactions: Cust_Code, Date, Details, Debit, Credit I would like to generate a report in the foll manner, say from...
3
by: MartinR | last post by:
Hi, I'm still new to writing code in vba as I've only been introduced to access three weeks ago. I have written this code below and it executes but does not do what I want it to do. What I want is...
7
by: mahgnilla | last post by:
Hi, I was wondering if anyone could help?! I have a button on one form (Participant form) that opens another form at a specific record (Learning Session form with specific session for that...
1
by: Martin Bentler | last post by:
Right now, I have a form, locked records, on which I have a CommandButton that opens a second form (much like the first) but open for editing, plus allowing the user to manipulate other areas of the...
21
by: Dan Tallent | last post by:
In my application I have a form (Customer) that I want to be able to open multiple copies at once. Within this form I have other forms that can be opened. Example: ZipCode. When the user enters...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.