Please help!!

[sir_dan_mitchell]

I have tried hundreds of ways and wasted many hours on this, perhaps one of you could help!??

I have a 2 tables. One contains the majority of my data (call it the main table) and I have another table that contains details about each person (person table).

On my form I want to select a person from a combo box and for all the records not only to appear in that form, but to also copy them into the main table at the same time! I have created the relavant fields, but I just cant make it work!!

I have Name (that i want to select from a combo box), Code1, Code2 and Code that i want to transfer into the form and then into my main table.

Please can somebody help!??

 

[lynsey2]

Sounds like you

a) need a subform to get all your selected persons records on your form

and

b)need to make your form bound to the master table!

im not too great but that looks like it might help with what you are trying to do....if that is what you want to do of course.

 

[lynsey2]

or
Create a combo box on your form, in the Afterupdate property put this code(not the private sub bit)

Private Sub Combo43_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number] = " & Str(Me![Combo43])
Me.Bookmark = rs.Bookmark
End Sub

Patient number is your 'persons name'from your person tbl and Combo43 is the combo box on your form!

Then

create text boxes for Code1, Code2 make these bound to your master table

 

[sir_dan_mitchell]

hey, thanks for ur help...i stil cant get it to work though.
What do I create in the combo box..lookup name and store as name in master table?

Do i write any of the below in the code?

' Find the record that matches the control.
Dim rs As Object

It says it cant locate my patient name!!

How do I make a text box 'bound' to anywhere? is it in control source?

Dan

 

[sir_dan_mitchell]

hey, thanks for ur help...i stil cant get it to work though.
What do I create in the combo box..lookup name and store as name in master table?

Do i write any of the below in the code?

' Find the record that matches the control.
Dim rs As Object

It says it cant locate my patient name!!

How do I make a text box 'bound' to anywhere? is it in control source?

Dan

 

[Hayley Baxter]

To make a form bound to a table you can do one of the following two things:

1) Use the wizard to create your form where you will select the table/Query the data comes from

2) You can build a form from scratch without the wizard and in the form properties you will select the table/query where the form will get it's data under the record source property.

The code Lyns gives you above is automatically generated by access if you use the combo box wizard this will automatically start up when you choose to place a combo box on your form, from here select option 3 to find a record on your form and the wizard will do th rest for you.

HTH
Hay

 

[sir_dan_mitchell]

thanks guys,

only problem im still not getting is how to get the codes to store in the tables. Ive been told to create text boxes etc, but im not 100% sure what to do now that ive done that combo box + code.

Dan

 

[Hayley Baxter]

Hi Dan

If you have text boxes on your form you can add data to the table by puting an add command button on your form again this can be done by using the wizard. The tables store the data so this is where your codes will be saved, as long as your form is based on the table or query you can view and add to your table from the form.

HTH
Hay

 

[sir_dan_mitchell]

i dont know whether ur understanding quite what I want!?

I have two tables. I want to select the name from the sub table and want the other fields that are also in the subtable to be brought into the master table automatically. i.e. I want to select one field from the subform, on doing this i want the 3 codes that apply to that person to copy into the master table!!!!

Sorry for my lack of brain!

 

[DaniBoy]

Little Confused!!

What data is in your Main Table? and What data is in your Person Table? Are the Main Table and Person Table related in any way? When you say "transfer" the codes, do you mean type in the codes on text fields or are you importing the codes?

"On my form I want to select a person from a combo box and for all the records not only to appear in that form, but to also copy them into the main table at the same time!"

Does the person have many records? or do you mean you want to see the persons details? By this I mean once you pick a person on the combo box, you want to be able to see "example: Name, Address, Tel# ect" or something like all their "invoices"



DaniBoy

 

[sir_dan_mitchell]

ok, each person in the person subtable has a postcode, GP code etc.

Previously where I have been using other tables from the main table it has only been necessary to store one record as a result of selecting the name from a combo box...and the ID would be sent back into the main table where it would be stored.

Here however I want to select the name from a combo box, but need about 3 other codes to copy and paste into the master table once I select a particular name. This is because these codes are specially assinged to each person and selecting each one each time would take ages!

Copying the records would also mean that when I later come to producing reports, these codes would show up in them!!!

In my main table I have other fields saying when a person have recieved medical treatment, the length of the placement, invoice dates etc etc etc.!

I gues the ID of the subtable and the ID of the main table are related.

Each person will have one record! so i want to look them up using a combo box to activiate the rest...!

Please help I have wasted so much time over this now!!

Dan

 

[DaniBoy]

Pat is right!! awlways right!!

Dan,
Start by starting all over again, I know this sounds realy bad, believe me!! I have done it!! Ask your self this questions:

What do I want the Database to do?

Ex: I want to keep records of patients treatments, ect.

What are my enteties on my datatbase?

Ex: You have patiens, you have treatments, ect

What fields relate directly to my entity?

Ex: In Patiens you want PatientID, PatName, PatRoomNum
ect. then go to the next one and so on. "ONLY WHAT RELEATES DIRECTLY TO THE ENTITY"

How does each table "Entity" relate to each other?

EX: Patient relate with Treatment, ect

After you have all this, you then start with queries, with queries you manipulate all the data.

Try doing that, also if you database is small zipped it and send it up.

DaniBoy