Auto populating problem

[lindabaldwin]

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_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

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

[nico5038]

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)

[lindabaldwin]

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

[nico5038]

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 CustomersSubform 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)

[lindabaldwin]

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 CustomersSubform 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

[nico5038]

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)