I've been puzzling over something, and hopefully someone here can help me figure it out.
In Access 2000, I have two forms... let's call them formA and formB. Users enter data first into formA. Then in certain circumstances, they need to complete formB. Four of the controls on FormB pull identical information as 4 of the controls on formA. I want to set up a command button on formA that opens formB and automatically populates these 4 controls.
My question is how to make this happen.
Can it be done with formA based on tableA, and formB based on a query combining tableB with the 4 fields from tableA, joining on the primary key [ID] from tableA and foreign key [IDtableA] from tableB? I can't get this to work.
At best, I've created a control on formB with [IDtableA] as source -- when I manually enter the corresponding [ID] number, the other corresponding fields will populate automatically. But I don't want to have to enter that number manually.
Do both forms have to be constructed based on the same table? Do my fields from tableB have to be incorporated into tableA to make this work? If formB were a subform directly on formA, it would automatically pick up that ID number. Isn't there some way to make this happen when opening formB through a command button on formA? It's kind of an 'external subform', at least in my mind...
Angi
18 32692 ADezii 8,834
Recognized Expert Expert
I've been puzzling over something, and hopefully someone here can help me figure it out.
In Access 2000, I have two forms... let's call them formA and formB. Users enter data first into formA. Then in certain circumstances, they need to complete formB. Four of the controls on FormB pull identical information as 4 of the controls on formA. I want to set up a command button on formA that opens formB and automatically populates these 4 controls.
My question is how to make this happen.
Can it be done with formA based on tableA, and formB based on a query combining tableB with the 4 fields from tableA, joining on the primary key [ID] from tableA and foreign key [IDtableA] from tableB? I can't get this to work.
At best, I've created a control on formB with [IDtableA] as source -- when I manually enter the corresponding [ID] number, the other corresponding fields will populate automatically. But I don't want to have to enter that number manually.
Do both forms have to be constructed based on the same table? Do my fields from tableB have to be incorporated into tableA to make this work? If formB were a subform directly on formA, it would automatically pick up that ID number. Isn't there some way to make this happen when opening formB through a command button on formA? It's kind of an 'external subform', at least in my mind...
Angi
There need be no correlation between the Forms whatsoever, or they may be related, the choice is yours. You are simply transferring data from one Form to the next. The one thing you must be aware of, however, is to make sure the Data Types of the underlying Fields are consistent within the transfer. For instance, if you are transferring the Text Value "One" from [Field1] in FormA to [Field1] in FormB, where the Control Source in [Field1]/FormB is Numeric, you will eventually get a Data type mismatch Error. -
'Transferring Field Values from FormA to FormB via the Click() Event of a Command Button
-
DoCmd.OpenForm "FormB"
-
-
Forms!FormB![Field1] = Me![Field1]
-
Forms!FormB![Field2] = Me![Field2]
-
Forms!FormB![Field3] = Me![Field3]
-
Forms!FormB![Field4] = Me![Field4]
There need be no correlation between the Forms whatsoever, or they may be related, the choice is yours. You are simply transferring data from one Form to the next. The one thing you must be aware of, however, is to make sure the Data Types of the underlying Fields are consistent within the transfer. For instance, if you are transferring the Text Value "One" from [Field1] in FormA to [Field1] in FormB, where the Control Source in [Field1]/FormB is Numeric, you will eventually get a Data type mismatch Error. -
'Transferring Field Values from FormA to FormB via the Click() Event of a Command Button
-
DoCmd.OpenForm "FormB"
-
-
Forms!FormB![Field1] = Me![Field1]
-
Forms!FormB![Field2] = Me![Field2]
-
Forms!FormB![Field3] = Me![Field3]
-
Forms!FormB![Field4] = Me![Field4]
I think there needs to be one more step in the code... is there a way to make it open FormB to a new record for data entry? Otherwise it appears to be overriding the data in an existing record.
Or do I need to set up FormB with the data entry property set to "yes", and save the same form as FormC with data entry set to "no" for reviewing all records?
ADezii 8,834
Recognized Expert Expert
I think there needs to be one more step in the code... is there a way to make it open FormB to a new record for data entry? Otherwise it appears to be overriding the data in an existing record.
Or do I need to set up FormB with the data entry property set to "yes", and save the same form as FormC with data entry set to "no" for reviewing all records?
is there a way to make it open FormB to a new record for data entry?
- DoCmd.OpenForm "FormB", acNormal, , , acFormAdd, acWindowNormal
- DoCmd.OpenForm "FormB", acNormal, , , acFormAdd, acWindowNormal
Thanks for the help!
Angi
Thanks for the help!
Angi
It occurs to me that there should only be one FormB for each FormA. I don't want a user to create a second FormB if one already exists. I've set it so that FormB contains a control (let's call it control5) that contains the primary key for FormA, and allows no duplicates. But a user could go about entering lots of data in FormB before trying to save it and getting an error message. So I'm wondering if it's possible to create an if-then statement that says "If this ID number already exists in control5 of any record of FormB, then open FormB to that record; else open to a new record."
Can this be done?
Angi
ADezii 8,834
Recognized Expert Expert
It occurs to me that there should only be one FormB for each FormA. I don't want a user to create a second FormB if one already exists. I've set it so that FormB contains a control (let's call it control5) that contains the primary key for FormA, and allows no duplicates. But a user could go about entering lots of data in FormB before trying to save it and getting an error message. So I'm wondering if it's possible to create an if-then statement that says "If this ID number already exists in control5 of any record of FormB, then open FormB to that record; else open to a new record."
Can this be done?
Angi
You could test the Record Source of Form B, something like: -
If DCount("*", "<Record Source for Form B>", "[ID] = " & Forms![FormB]![Control5]) > 0 Then
-
'ID already exists
-
Else
-
'proceed as Normal
-
End If
You could test the Record Source of Form B, something like: -
If DCount("*", "<Record Source for Form B>", "[ID] = " & Forms![FormB]![Control5]) > 0 Then
-
'ID already exists
-
Else
-
'proceed as Normal
-
End If
Adding this code, I'm getting an error message saying that the database can't find FormB. I know I've got it spelled correctly. Any ideas?
Adding this code, I'm getting an error message saying that the database can't find FormB. I know I've got it spelled correctly. Any ideas?
I've been trying to work around this, but I'm still having this problem - the database can't find FormB. Can anyone help me out?
ADezii 8,834
Recognized Expert Expert
I've been trying to work around this, but I'm still having this problem - the database can't find FormB. Can anyone help me out?
Does a FormB physically exist in your Database?
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Matteo Settenvini |
last post by:
Ok, I'm quite a newbie, so this question may appear silly. I'm using
g++ 3.3.x.
I had been taught that an array isn't a lot different from a pointer
(in fact you can use the pointer arithmetics to "browse" it). So I
expected that when I run this program, I get both c1.A and c2.A
pointing to the same address, and changing c1.A means that also c2.A
changes too.
----- BEGIN example CODE -----------
|
by: Iain Miller |
last post by:
Struggling a bit here & would be grateful for any help.
I have a table which has a list of people in it. Each person has a unique ID
automatically allocated by Access but also belongs to one of 5 Groups - call
them A to E. I'd like to generate a further automatic reference number based
on something like Group/Unique ID so when I create a new record Access
creates the Unique ID & I then enter in the group and then Access combines
the two...
|
by: Andy Hutchings |
last post by:
Hi everybody - hope you can help out here.
I have a form in a database, which is a columnar form from one of the tables
in the db - there is a sub-form to the form which is a datasheet view of
another table in the db. The LinkChilds property is set to show connected
records from both tables.
What I'd like to do is copy and paste, or use some other method of writing,
the contents of one field in one table in the form, to a field in the...
|
by: Christopher Koh |
last post by:
how do you stop Access from saving any changed data in your tables and
queries?
like i just add or change data on the table/query tables,then click on
X (exit)because i have no intention of saving it but access still
automatically saves it even if I did not press the save command on the
menu/toolbar? What is the solution for this? help thanks!
|
by: meganrobertson22 |
last post by:
hi everybody-
what is the best way to add data from one form to another?
i have 2 tables: person and contract. here are some of the fields.
table: person
personid (autonumber and primary key)
ss#
| |
by: Martin Ho |
last post by:
I am running into one really big problem.
I wrote a script in vb.net to make a copy of folders and subfolder to
another destination:
- in 'from.txt' I specify which folders to copy
- in 'to.txt' I specify where to copy it
- After I read content of 'to.txt' I create one more subfolder named
by current date and thats where everything gets to be copied
|
by: zMisc |
last post by:
Is it possible to copy a table from one schema to another schema by just
copying the frm file to the directory for the new schema?
What is the best way to create a new database with all the tables in it
automatically?
I was hoping to have the tables (the frm files) included in a subdirectory
and when required, just create a new schema then copy all the frm files into
it.
|
by: daD |
last post by:
I'm trying to write a small database that tracks people coming and
going from a small campground. I need to have the current guests in
the "current" table" and then have the ability to check them out to
the "archive" table when they leave, by pushing the "check-out"
button. I see the steps as follows:
1. When the check-out button is pressed, the computer should
automatically add the current date into the "date checked-out" field.
...
|
by: saiprasanthi |
last post by:
Hi,
I want to copy data from one text box to another in html automatically ie., as I edit the first text box the second one should reflect the same spontaneously... can you help me???
Thanks in advance.
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |