473,327 Members | 2,094 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,327 software developers and data experts.

Link 2 Forms Bound to Same Table

mjoachim
I currently have a primary table with 20 fields. My main form contains controls to enter data that will populate 15 of those fields. The other 5 fields are a special subset of data that most records will not utilize.

To keep my main form clean and easy to read, I set up another form that would allow the user to enter the 5 fields that aren't on the main form. Within the main form, I provided a command button to pull up the other form. That all works well, but when the 2nd form is opened from the main form, data entered is not linked to the open record of the main form and instead creates a new record.

Is there a way that I can link the 2 forms together so that upon opening the 2nd form and entering data, those entries populate the record that is active in the main form?

I know this can be accomplished using a subform, but these are additional fields that I'd rather not have always displayed on the main screen.

Any help is greatly appreciated!
Jul 17 '15 #1

✓ answered by zmbd

You can set the visible property to false.
You can lock those fields
You can disable those fields.
A combination of the above.

To your particular design:

What I would do is open your second form for record editing
(DoCmd.OpenForm Method (Access)) passing to it the wherecondition such that the only record shown is the current record.

The link above shows an example of how to use the where condition, I would alter this slightly as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdShowOrders_Click()
  2. Dim sSQL as string
  3. Dim sFormName as String
  4. sSQL = "CustomerID=" & Me.txtCustomerID
  5. sFormName = "YourFormName"
  6. If Not Me.NewRecord Then
  7.     DoCmd.OpenForm _
  8.       FormName:= sFormName, _
  9.       DataMode:= acFormEdit, _
  10.       WhereCondition:=sSQL
  11. End If
  12. End Sub
Note the use of the string variable, this allows you to more easily debug the code because you can then use the debug.print to obtain the resolving string... root of easily 70% of all problems. Also I've inserted the datamode as an example, with this set, one can only edit existing records; however, one can not add records. The wherecondition will filter your form down to the condition you are after. If you have set a primary key (HIGHLY ADVISED) you can then create the condition that your code passes at form open for the current record.

BOL... post back if you have problems.

6 2041
zmbd
5,501 Expert Mod 4TB
You can set the visible property to false.
You can lock those fields
You can disable those fields.
A combination of the above.

To your particular design:

What I would do is open your second form for record editing
(DoCmd.OpenForm Method (Access)) passing to it the wherecondition such that the only record shown is the current record.

The link above shows an example of how to use the where condition, I would alter this slightly as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdShowOrders_Click()
  2. Dim sSQL as string
  3. Dim sFormName as String
  4. sSQL = "CustomerID=" & Me.txtCustomerID
  5. sFormName = "YourFormName"
  6. If Not Me.NewRecord Then
  7.     DoCmd.OpenForm _
  8.       FormName:= sFormName, _
  9.       DataMode:= acFormEdit, _
  10.       WhereCondition:=sSQL
  11. End If
  12. End Sub
Note the use of the string variable, this allows you to more easily debug the code because you can then use the debug.print to obtain the resolving string... root of easily 70% of all problems. Also I've inserted the datamode as an example, with this set, one can only edit existing records; however, one can not add records. The wherecondition will filter your form down to the condition you are after. If you have set a primary key (HIGHLY ADVISED) you can then create the condition that your code passes at form open for the current record.

BOL... post back if you have problems.
Jul 17 '15 #2
I was exploring an approach similar to your suggestion, but my problem is what field to use for linking.

(A little background: this is a form used to enter time data and populate a file that will be used to upload data into another system weekly. Because of this, each employee would have many records and there is no field on the form that is truly unique to a record.)

Does Access have a "hidden" record ID that could be referenced in place of CustomerID as in your example?
Jul 17 '15 #3
zmbd
5,501 Expert Mod 4TB
no
Either You need to set the primary key and use that for the filter or find a set of fields such that:
([field1]=something1) AND ([field2]=something2)... (repeat)) identify the record uniquely.
Take a look thru: Database Normalization and Table Structures
It really sounds like there's something amiss in how your table(s) are structured.

Please also check your bytes.com inbox as I will be forwarding a copy of a list of basic tutorials and resources that I've found useful over the years.

-z
Jul 17 '15 #4
I do have tables with primary keys for information that my form references. But since the table that my form is Bound to is for time entry, there can be many entry lines per employee per day, which eliminates any possible unique values. I can add a RecordNumber field to my table which would provide a unique identifier, but if I do that, are you aware of any good way to prevent that field from exporting?


If properly linking them provides too many challenges, I can simply use InputBox to do essentially the same thing...it just looks uglier and requires more boxes.
Jul 17 '15 #5
zmbd
5,501 Expert Mod 4TB
IMHO
Every record in every table should have a unique primary key.
This is what the autonumber/guid field is for, refer to the normalization link in my prior post.

So in your case, IMHO, you should have something like the following going on in your database:

(in the following, [PK] is an autonumber field typecast long
[FK_*] is a Field typecast Numeric(long) with a one to many relationship set to the [employeetable]![PK] enforced no cascades

[employeetable]
[employeetable]![PK]
[employeetable]!{....other related fields, Family name, First name, etc.

[employeetimetable]
[employeetimetable]![PK]
[employeetimetable]![FK_EmployeeTable]
[employeetimetable]!{date and time, other related fields}


So example
[employeetable]
[PK][some related fields....]
[1][familyname1][firstname1][MI1][BadgeNumber1]
(.... many records ....)
[255][familyname255][firstname255][MI255][BadgeNumber255]


[employeetimetable]
[pk][fk_employeetable][some related fields]
[1][1][2013-07-16 13:00][2013-07-16 23:00]
[2][16][2013-07-17 13:00][2013-07-17 23:00]
[3][255][2013-07-18 13:00][2013-07-18 23:00]
[4][255][2013-07-19 13:00][2013-07-19 23:00]
[5][16][2013-06-15 13:00][2015-07-17 23:00]
(.... many records ....)
[1200][1][2014-07-16 13:00][2014-07-16 23:00]
[1201][16][2014-07-17 13:00][2014-07-17 23:00]
[1203][255][2014-07-18 13:00][2014-07-18 23:00]
[1204][255][2014-07-19 13:00][2014-07-19 23:00]
[1305][16][2014-06-15 13:00][2014-07-17 23:00]
(.... many records ....)
[6151][1][2015-07-16 13:00][2019-07-16 23:00]
[7562][16][2016-07-17 13:00][2019-07-17 23:00]
[9515][255][2017-07-18 13:00][2019-07-18 23:00]
[9616][255][2013-07-19 13:00][2019-07-19 23:00]
[9999][16][2018-06-15 13:00][2019-07-17 23:00]

Now if I clicked on your button, and the current record is
[employeetimetable]!
[7562][16][2013-07-17 13:00][2013-07-17 23:00]

Then the value I'd pass in the code would be:
[employeetimetable]![PK]=7562

Or lookat [employeetimetable]![PK]=9616 ... note how you can find that record without any other information! You know exactly that [employeetable]![PK]=16 is the employee (which now with query you can pull that employee's name from the [employeetable], and you can also filter the [employeetimetable] for that employee, feed those records to the edit form

and

now your form knows exactly which record(s) to filter down to in the recordset, the user can only edit the record(s) (using the concept earlier) and all is good in the Emerald City...


Normalization usually keeps the Flying Monkeys from shredding your mind!

>>> as for not exporting the PK field or FK... build your query and do not include it in the visible fields...

Expand|Select|Wrap|Line Numbers
  1. SELECT [field1], [field2]
  2. FROM [table1]
  3. WHERE ([Field3] = 16);
Of course the "16" could be a variable/parameter based value. You would export this query as normal and only the records where Field3=16 would be exported and Field3 would not show up in the exported data.
Jul 17 '15 #6
Thank you for all of your insight. I'll see what I can do to keep the Flying Monkeys at bay.
Jul 17 '15 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Brian Kedersha | last post by:
We created a XML Schema that has nested table relations. We had the following Warning message come up. An unhandled exception of the type 'System.ArgumentException' occurred in system.data.dll...
2
by: TheTamdino | last post by:
Hello all, I've tried to work this one out myself, but appearantly my brain is just a little wacked right now and I need some feedback. I'm trying to design a genealogy database (mainly...
2
by: Kevin | last post by:
Hello, How do I concatenate two fields in the same table... For example, I have two tables, first one is called familynametable that has a familyID and familyname field. This table is connected...
0
by: Redstone | last post by:
When attempting to refresh a link through ODBC to a table (T_Leases) in my SQL backend, I get an error message teling me the Jet Engine can't find an object (A differnet table T_LockTypes in the...
3
by: tlyczko | last post by:
I have a main form with 2 subforms, each subform references different data in the same table. How do I make sure each subform only creates NEW records in the referenced table and do not...
1
by: keliie | last post by:
I have a relatively simple (I assume) issue which I am at a complete loss to address. My issues is: I want to populate fields in my tables with summary data from the same table. Let me explain: ...
6
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called...
5
by: binky | last post by:
Question for all you SQL gurus out there. I have a (seemingly) complex query to write, and since I'm just learning SQL server, am not sure how to go about it. Tables: t_trans t_account All...
2
dlite922
by: dlite922 | last post by:
I have a permission table that gives a userID permission to a module and the any actions within that module. What I want to do is duplicate his permissions to another user. Permission table...
71
by: julienmy5757 | last post by:
Hello, I am trying to modify (in a continous form) two differents records when you modify one.. I can see only one in the form. The records are coming from the same table. Error comes from...
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...
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: 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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
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.