Add New Record to Subform on a Tab Control

[shelby55]

Hello

I'm using Access 2003.

I have a database where I'm entering informatino to add clients to the clients table. On page 3 of the tab control I have a subform which is used to collect the clients' visits to the office (so one to many relationship). Since I'm doing this from scratch, the table currently doesn't have any data in it.

Anyway, the subform is linked by client chart number and the subform properties are set to allow edits = Y but I can't edit it to add visits. What do I need to do for that to happen?

Also, is there any way to programatically add visit occurrences i.e. when I add a record #1 will show in the occurence field, 2 for the next one and so on? Thanks.

 

[TheAceMan1]

How are ya shelby55 . . .

[blue]Anyway, the subform is linked by client chart number ...[/blue]

How can a [blue]chart number[/blue] represent a client when [blue]a client can have many charts?[/blue]

Typically this secnario uses the [blue]primarykey[/blue] of the parent form as the link and is typically something like [blue]ClientID[/blue] (used as the foreignkey in the subform). Chart Number should be an item added in the subform. Your Tables should look something like:

[tt][blue]tblClients
**********
ClientID as AutoNumber PrimaryKey
FirstName as Text
LastName as Text
Address as Text
OtherFields ...

tblVisits
*********
VisitID as Autonumber PrimaryKey
ClientID as Long [purple]ForeignKey[/purple] to tblClients.ClientID
VisitDate as Date
VisitTime as Time
ChartNum as Long
OtherFields ...[/blue][/tt]

[blue] ... is there any way to programatically add visit occurrences i.e. when I add a record #1 will show in the occurence field ...[/blue]

Since you'll be logging visit dates this should alleviate numbering visits. In the form you can simply show the record count in the subform.

[blue] ... the subform properties are set to allow edits = Y but I can't edit it to add visits ...[/blue]

Its possible your using an uneditable recordsource for the subform. Post back what this is.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[shelby55]

Hi Ace

Thanks for replying.

You are correct: the chart number is the number that is with a patient for life. Each visit will have a different account number so the combo of chartno and account number will be unique. But I want all visits specific to the chart number so why wouldn't that be my link?

So I want to add new chart, enter information on the first 2 pages which are singularly occurring (address, name, DOB) and then on page 3 will be for visits of which there could be more than one.

I know that when I add the record the chart and/or acct number won't be saved yet so I have the record being saved going into page 3 but it still doesn't work.

Not sure what you mean by "uneditable record source"? Thanks.

 

[TheAceMan1]

shelby55 . . .

Understand on the [blue]Chart Number[/blue] (your latest post should've been a part of the 1st). I've done quite a few DB's in the medical expertise and [purple]this is the 1st time a chart ID's a client.[/purple]

You need to supply some assemblence of your table structure if were to go further. Take a PrintScreen of your relationships window and upload it to 4Shared. You'll be provided with a link you can post. Can't make any headway without it!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

shelby55 . . .

Whats the [blue]RecordSource[/blue] property of the mainform & subform? If a query post the SQL ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[shelby55]

Hi

Thanks for still trying, Ace.

For the main form it's the table of "tblpatients" which has chart number and account number and the subform is the table "tblVisits" with the same data elements. For both, I created the unique index out of the chart number and acct number together and made it unique and primary.

 

[shelby55]

Hi

Okay, I've been doing some testing and if I just take a form and add the code for the oncurrent of the main form:

me.sfrmvisit.form.allowadditions=true

then the subform is editable.

I have the current main form programmed so that the record saves on exit and I have to select an Edit Record button to allow edits. So I added the code above to the edit button and it works.

But can I add this allow additions code to the tab control i.e. if on page 3 then allow additions (for when I have new records)? This may be for another post but is there any way to programatically add in occurrence # so row 1 is 1, row 2 is 2 etc.?

Thanks!

 

[TheAceMan1]

shelby55 . . .

Can you edit if you link the subform on both fields?


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[shelby55]

Hi Ace

Yes it is editable with using both fields as the linking field. Does that make sense to you?