Open form with specific record

[Lukael]

Hi,



I have two tables which are linked in many-to-many relationship, and both have forms.
Subform is added on main form to save records via primary keys. I wish to open second form in same recordset I entered in main form. Here's what I tried:

Private Sub Command1_Click()
Dim recordID As Integer
recordID = Me.ID
DoCmd.OpenForm "Form2", , , "ID ="  & recordID
End sub

With this code I get msgbox that is asking me to enter id

Any help appreciated !

[ItsMe]

Look at Form2's recordsource and be sure to add the field 'ID' to the form's recordsource. The ID field should be available from the List of Fields when you click the Add Existing Fields button from within the Ribbon. However, you do not need to add the field to the design of your form. It only needs to be included in the Recordsource and available.

[Lukael]

It doesn't work. Please take a look at this sample. Same principle as in mine DB.

Open "Table1" form. You'll see combobox for Surnames . This combo stores record in joined table. Then click to CmdButton next to It - It should open "Table2" form in record no2 (based on entered data at the moment).

[ItsMe]

You do not have a field named ID in the recordsource of the Form named Table2. I did find a field named ID2

[Lukael]

I really don't understand what does name have to do with It - I have tried with "ID" name of Autonumber field for both tables in every possible combination (first table with ID, second and both with ID field) and It's still not working. I also added another field named ID, and still not working. So where am I wrong ?

EDIT: forgot to mention - code works this way:

Dim recordID As Integer
recordID = Me.ID1
DoCmd.OpenForm "Table2", , , "ID2 =" & recordID

But this only opens second form, and not in desired record. Form should open with record no2, but opens in no1.

[ItsMe]

Perhaps you should try the following code ...

Private Sub Command1_Click()

Dim recordID As Integer
recordID = Me.ID

msgbox recordID

End sub

[Lukael]

I'm sorry, but this is completely wrong, that's why I posted sample to understand better. Let me explain again, differently :

1. I enter data on 1st form ("Table1" form in sample). With combobox on form I select value from 2nd table. When I do this, tables are joined and in joined table is created new record (in my sample "ID1" value 1, and "ID2" value 2). This links 1st and 2nd table.

2. When I click to Cmdbutton, form 2 ("Table2" in sample) should be opened in record that is linked, as value in linked table for "ID2" is 2.

So, in my case we see on form "Table1" entered data "John" "Reaming", and Cmdbutton on It should open form "Table2" in record no2, where "Reaming" is saved.


My code just defines number of currently opened ID, and then assignes this number to ID of second table, WHICH IS WRONG. In my sample this code opens form "Table2" on record no1...


I need code to check my currently opened ID number of form, then maybe check rows with that ID number in joined table, and then display record of ID2.

[ItsMe]

Perhaps if you start a new thread and reference this one, you will get the attention of other members here. There is not much more advice I can offer. You need to figure out what value to apply to your criteria. If Me.ID is not the correct value, than you need to assign a different number to your variable (recordID). The code in post #6 will show you the value of recordID. Look in a table and see if this is the value you want to apply to your criteria. Determine what value you want to apply to your criteria. Determine where, in your original recordset, this value exists and how to apply the appropriate value to recordID.

[orange]

I suggest you tell us in plain English what you are trying to accomplish. Tell us about you business and where this database fits. Pain English until we understand what. Then readers will offer suggestions.

When you have this situation

I have two tables which are linked in many-to-many relationship, and both have forms.

it indicates there may be a table or two missing.
To handle a M:M relationship in database, you add a third table (a junction or linking table). The PK of the new junction table is composed of the PKs of each of the original 2 tables.

eg: tblStudent<----->tblCourse

pk StudentID.............pk CourseIdD

where many Students take many Courses
and Many Courses are taken by many Students

tblStudent----->jncStudentTakesCourse<------tblCourse

..................... pk (StudentID,CourseID)


More info Junction table

[Lukael]

Thanks for responses.

I don't know how to explain more easily than in my prevoius post. But....

Open my sample and take a look at combobox in form "Table1". Value that is in there, that record should be opened when clicking on Cmdbutton next to It.

Can't go more easier than this

[orange]

Lukael,

Although you haven't told us explicitly and clearly what you are trying to accomplish in plain English, I have looked at your database and guessed that you are trying to populate the Junction table.
I created a Form, called frmToAddRecordToJunctionTable. It contains 2 comboboxes combo2 and combo4. Combo2 is populated with records from your Table1 -which is poorly named in my view. Combo4 is populated with records from your Table2 - which also is poorly named in my view.

When you select a FirstName value in combo2, and a Surname value from Combo4, you can add a new record to your JoinTable by clicking the command6 button which has a caption
"Click to Add This new Junction Table Record"

The click event has the following code.

Private Sub Command6_Click()
'This procedure is executed when the command6 button is clicked.
'You do this to add this coombination FirstName and Surname to the
'junction table which you have named JoinTable

    Dim sql As String
    On Error GoTo Command6_Click_Error

    sql = "INSERT INTO  JOINTABLE (ID1,ID2) VALUES (" & Me.Combo2 & "," & Me.Combo4 & ");"

    MsgBox " New record is " & Me.Combo2.Column(1) & "  " & Me.Combo4.Column(1) & vbCrLf _
         & " which will be stored as " & Me.Combo2 & " ," & Me.Combo4 & "  within the JoinTable"
    CurrentDb.Execute sql, dbFailOnError

    On Error GoTo 0
    Exit Sub

Command6_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command6_Click of VBA Document Form_frmToAddRecordToJunctionTable"

End Sub

I changed a few things in your JoinTable. I added a composite unique index composed of fields ID1 and ID2. This will prevent duplicates in the JOINTABLE.

I added 2 queries to the database
qryShowAllJoinTableRecords ---shows all records as they exist in JoinTable
qryShowAllNamesAndSurnamesOfJoinTableRecords -- uses relationships to show the values of FirstName and Surname based on the record in the JoinTable.
You could add buttons to frmToAddRecordToJunctionTable to open these queries if you wanted.

A few points regarding your database and databases in general using MSAccess.

Name tables, fields and objects with meaningful names. Tables can be FirstNameTable or tblFirstName etc. Calling tables Table1 and Table2 does not communicate anything to readers, nor anyone who may be left to manage your database. Name forms with a meaningful name also --calling a form "Table1" is confusing to say the least. Better to give your objects unique names, but this comes with experience.
Similarly in JoinTable, you have 3 fields - ID, ID1 and ID2. Naming the ID field simply ID in all tables ill lead to confusion especially when you get to 20-30+ table databases. A best practice is to name the ID field with some representation of the table -- for example SurnameID, FirstNameID.

I did not rename your tables or fields. I left my combo2 and combo4 as Access named them. I do not use macros, and prefer vba. M$oft recommends use of Access/Office 32 bit unless you have special requirements.


As I said I'm guessing at your requirement. If I have guessed wrong, then I suggest you give a clear description of what you are trying to accomplish in simple, plain English --and use business terms not database or Access jargon.

Good luck with your project.

[Lukael]

Thanks for all your effort Orange.

I really really don't understand how you don't understand what I need

What you did is nice, but not even close to what I want... You didn't need to change anything in my sample. I allready ADD RECORDS in junction table in my sample, that's by selecting value in Combobox (form "Table1"). You can check that, you'll see that records are added automatically. But that record is like in your sample just index to another table---- like let's say like 1 and 2:

- 1 represents record number of first table;
- and 2 represents record number of second table;

And all I want is to open that record number 2, so when clicking on CmdButton form "Table2" should open, and on form's display there should be record no2.

I allready achieved that by creating a junction form, which is opened by CmdButton, and on that form you can then select another CmdButton which then finally opens desired record. But I want to do this directly with VBA, without any additional forms.

It is just a simplified view of joined record from second table, that will allow users to see what compile of data did they link to their "master" record. Something like this, example:

- APP is software;
- PC is hardware;

APP, like "MS Access" can be installed on plenty PC's, so all PC's with this APP must be added to APP record. And when user want's to see each PC where this APP is, he should simply click and PC with that record should be listed.

I am aware that my names for forms in sample are a little bit confusing, but that is just a sample, in real life I name forms/queries/tables like you suggested.

[orange]

Lukael,

We are obviously not communicating on what you are trying to accomplish. So one more try.

Suppose you are in a line up at McDonalds.The person ahead of you who doesn't know you, has never heard of database and has no knowledge of tables or fields, but is a friendly sort. He asks you what you're working on -in as simple language as possible. Remember he knows nothing of you, database etc.and he wants to know what you are trying to accomplish.
What exactly do you tell him?

[JeroenMioch]

When i select the record of Jhon (1)
And i open up form2 then i get Deere (1)

When i select the record of Mike (2)
And i open up form2 then i get Reaming (2)

If that is not what your trying to accomplish then im clueless like the rest :P

Dim sWhere As String
sWhere = "[ID2] = " & Me.ID1


DoCmd.OpenForm "table2", acNormal, , sWhere

Oh and on another level i would name textboxes on the forms beginning with 'ctrl'
So your ID field would be called ctrlID, Surname field 'ctrlSurname' and so on.

Same practice with tables and forms. Name your forms to what they show and put frm in front of it.
So in stead of Table1 you name the form frmFirstNames.
Likewise with tables, tblFirstNames, tblSurnames.. You catch my drift

Much easier that way to code, because your referring to understandable objects as opposed to numbers (table1, table2, ect)

Just a tip.

[Lukael]

Hi Jeroen,

no, It's not correct, but I think you may be closer than others, at least you included ID2 in code.

When you open sample in form1, you see John and Reaming - correct ? John is entered in field, Reaming is listed value in combobox....So, when you click on Cmdbutton, you should see Reaming on form2 - that would be correct !!