473,721 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Auto populating problem

21 New Member
Hi,

I am having a really difficult time with this, so any help is very much appreciated. I have created a form with two subforms. The parent form includes patient data. The first subform contains appointment data. The second subform contains delivery data. The three tables are linked by member record number. I have two problems that I have been trying to figure out for weeks!

The first is that I want the two subforms to populate with the data based on the member record number that is typed in...unless it is the first time data is entered for that patient. It was working just fine, but then I deleted the relationships so that I could rename the member record number field. After I reestablished the relationships, this feature no longer worked. I have no idea what happened!

The second thing that I am trying to do is to enter the member record number and have the remaining patient information populate, such as first name, last name, etc. Currently, when I update the member record number field, nothing happens. The medical record number is text. Here's the VB code.

Private Sub Medical_Record_ Number_AfterUpd ate()
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClo ne

rst.FindFirst "[Medical Record Number] = '" & [Medical Record Number] & "'"

If rst.NoMatch = False Then
Me.Undo
Me.Bookmark = rst.Bookmark
MsgBox "Medical Record Number already exists!"
End If
End Sub

Anyone know what I am doing wrong?

Thanks for your help!

Linda
Oct 30 '07 #1
5 2009
nico5038
3,080 Recognized Expert Specialist
I assume you did delete the relation ship in the relationship window.
A subform has two linkage fields (single click the subform in design mode and open the Data tab in the properties window) and these should reflect the proper fieldnames for the Main and Subform field that defines the relationship.

To find a member record number, just add a combobox and select the option for a "Search" combo. Access will provide the needed code for the find, but make sure that the field is NOT bound to a tablefield.

Nic;o)
Oct 31 '07 #2
lindabaldwin
21 New Member
I assume you did delete the relation ship in the relationship window.
A subform has two linkage fields (single click the subform in design mode and open the Data tab in the properties window) and these should reflect the proper fieldnames for the Main and Subform field that defines the relationship.

To find a member record number, just add a combobox and select the option for a "Search" combo. Access will provide the needed code for the find, but make sure that the field is NOT bound to a tablefield.

Nic;o)

Hi Nic,

I did delete the relationships in order to change some things on the table, but I re-established the relationships. I tried what you recommended above, but I am not sure what the two linkage fields are. I clicked on the subform then the data tab and I only see the following fields: Record Source, Filter, Order By, Allow Filters, Allow Edits, Allow Deletions, Allow Additions, Data Entry, Recordset Type, Record locks, Fetch Defaults.

Also, although the combo box is useful in finding a member record number, I am not sure how to use that information to auto populate the other fields, such as, first name, middle initial, last name, physician, etc. I managed to auto populate the fields using DLookup using an AfterUpdate sub, but I am encountering some difficulties with that still. It does not auto populate the desired fields until after I click on a subform. Then it will not allow me to save changes to it because it says it would cause duplicate information on the primary key. I am really not sure what I am doing wrong. Any ideas?

I don't understand why you would not want the field bound to a table field. Would you explain that a little more?


Thanks.

Linda
Nov 1 '07 #3
nico5038
3,080 Recognized Expert Specialist
Hi Nic,

I did delete the relationships in order to change some things on the table, but I re-established the relationships. I tried what you recommended above, but I am not sure what the two linkage fields are. I clicked on the subform then the data tab and I only see the following fields: Record Source, Filter, Order By, Allow Filters, Allow Edits, Allow Deletions, Allow Additions, Data Entry, Recordset Type, Record locks, Fetch Defaults.

Also, although the combo box is useful in finding a member record number, I am not sure how to use that information to auto populate the other fields, such as, first name, middle initial, last name, physician, etc. I managed to auto populate the fields using DLookup using an AfterUpdate sub, but I am encountering some difficulties with that still. It does not auto populate the desired fields until after I click on a subform. Then it will not allow me to save changes to it because it says it would cause duplicate information on the primary key. I am really not sure what I am doing wrong. Any ideas?

I don't understand why you would not want the field bound to a table field. Would you explain that a little more?


Thanks.

Linda

You'll have to single-click the subform when you have the mainform in editmode.
The Linkage fields aren't visible when you double-click the subform as that will give the form properties of the subform.

When you need e.g. a customers address on an orderform you can add a CustomersSubfor m and link that with the linkage fields to the combobox so the correctaddress is displayed when you change the combo information.

Just give it a try.

Nic;o)
Nov 1 '07 #4
lindabaldwin
21 New Member
You'll have to single-click the subform when you have the mainform in editmode.
The Linkage fields aren't visible when you double-click the subform as that will give the form properties of the subform.

When you need e.g. a customers address on an orderform you can add a CustomersSubfor m and link that with the linkage fields to the combobox so the correctaddress is displayed when you change the combo information.

Just give it a try.

Nic;o)
Hi Nic,

Thank you for your assistance. I was able to view the linkage. It appears to be correct, but when I enter the linked information on the parent form (medical record number), it does not pull up the related data on the subforms. It just stares blankly at me like it doesn't know what I want it to do.

I have a question about the usage of the combo box. Is this strategy similar to a lookup table? I am not sure how the combo box method would work for me. The combox limits me to the records I already have in the table. It will not let me enter in new data. Am I doing this right?

Thanks.

Linda
Nov 1 '07 #5
nico5038
3,080 Recognized Expert Specialist
When nothing is made visible, the keyfields are probably incorrect.
I see spaces in your fieldname, this requires brackets [ and ] around it like:
[medical record number]

For the combobox you'll need to check the "NotInList" event that will allow the addition of new values. Check this description to learn all about it:
http://www.fontstuff.com/access/acctut20.htm

Basically you can have different working comboboxes, one to limit the values, one to expand the values and one for searching a record. You'll see that the Combobox wizard shows the possibilities when started for a new combobox.

Nic;o)
Nov 1 '07 #6

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

Similar topics

1
2281
by: David Shorthouse | last post by:
HI is it posible to auto populate an access database.? I have 1 table structured Index_ID. - Index & auto number First_Name - Char50 Surname - Char50 Initals - Char50 Postcode - Char50 Email_Address - Char50 Mailshots - tick box
7
12851
by: Yannick Turgeon | last post by:
Hello all, I'm using SS2K on W2k. I'v got a table say, humm, "Orders" with two fields in the PK: OrderDate and CustomerID. I would like to add an "ID" column which would be auto-increment (and would be the new PK). But, I would really like to have orders with the oldest OrderDate having the smallest ID number and, for a same OrderDate, I'd to have the smallest CustomerID first. So my question is:
3
12593
by: sao | last post by:
I am currently using Access 2000. In my table it is the following fields that are set up: 1 - Staff Name 2 - Department This table is already populated with 100+ records (staff along with department.)
1
1805
by: Jason Galvin | last post by:
I would like to disable the auto-populating feature (remembers form element text between post-backs) when creating a .NET form. I have succeeded in disabling auto-populate by creating my controls during PreRender, but that becomes highly cumbersome. Is there a way to explicitly turn off auto-populate? I'm pretty sure the form isn't getting auto-populated by the ViewState mechanism, because I've specifically set EnableViewState to...
1
9402
by: Thumper | last post by:
I have created a "Services" table in Access. Within this table I have a "Services Provided" combo box that lists various services I have populated in a "Products" table. I also have a "Default Price" field in the "Services" table. However, I would like for this field to auto-populate a value once a service is selected from the combo box. I have defined default price values in the "Products" table and am not sure how to have this data...
4
3427
by: JayV | last post by:
I've created a data entry form. I have multiple fields but the fields "Road Log #" and "Road Name" I want to be bound to each other. I also have a table that lists out 1200 road log numbers and their corresponding road log names. I want the number that is entered into "Road Log #" to auto populate the "Road Name" with the correct name from the table. Help? I don't know much about the expression builders. But I tried messing around with things...
2
2566
by: kkramer | last post by:
I have a table in Access which is auto populating fields which I do not want to happen. If I list a number in a field, and then another number in the field below it, and then a third number, if those numbers happen to coincidentally follow a pattern (for example, 10322,10324,10326), the fields below are auto populating to follow the same pattern. I can't seem to figure out how to shut that off--it must have been a default because it wasn't...
3
2174
by: Wayne L | last post by:
Ok now everyone has mentioned not to use auto number if it means anything to the user. My application uses the auto number for exporting only. I append the mastertbl column with my starting number of 0001(4 digits) (prior to populating the table). When information is imported and appended via a query to the mastertbl it will start with 0002 and increment. All tables used are cleared with a delete query after being exported, this allows the...
3
2512
by: Puzzled and Confused | last post by:
It seems like a simple request, but I cannot get it to work. Does anyone know to how to make data from one field auto populate in another field, only if the answer is yes/true? I have a form that I would like the exact data, which varies, to auto populate only after an update to yes. A toggle button is to answer the field, but I would like for the answer to be the data from another field. Please advise. Thanks!
4
4844
by: olidenia | last post by:
I need some help or tips on the following. I have a .sql database file with the folowing structure: DROP TABLE IF EXISTS `car`; CREATE TABLE IF NOT EXISTS `car` ( `id` int(10) default NULL, `Make` text, `Model` text,
0
8840
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8730
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9215
jinu1996
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...
0
9064
tracyyun
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...
1
6669
isladogs
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...
0
4484
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4753
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2576
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2130
bsmnconsultancy
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...

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.