COMBOBOX - RECORDS IN TABLE

[Samora]

Hello everyone

I have a table named TGenConsultaswith 3 fields

Numero Numeric
Data Date/Time Primary Key
Obs Memo

the field Numero is obtained from a Combobox based on a
Query that gives me the Number so i can store it in my table

Plus, I want that after i update my combobox, on my form
appears all the records that the field Number is equal to
the value i put on my Combobox.

At this moment i couldn't get this goal.
I think this will work like an SQL statement (Select * from
TGenConsultas WHERE Number = ComboBox.Value) something like
that i guess. i don't know quiet clear the sintax .
The fact that i have the field Data as primary Key ensures
to me that to that specific Number i will have no
duplicates and they will appear in ascending order as i
want to.

My form is a continuous form. Is this a good aprroach? Or
is better to make a subform? For the purpose i want i think
the continuous will be better, but i don't mind to change
it to subform , regarding the memo field.

So, on first stage i want that all the records that are
equal to the value on my combobox appear. (my combobox will
work as a filter) and after that, i can append more records
on my table filling the fields for that specific Number on
my form.

Is this possible?

Could someone help me?

I would aprreciate very much.

Thanks in advance

Best regards

 

[Guest]

Are you using a Date/Time field as the primary key? Autonumber would be a
better choice, particularly if the purpose is to provide a unique identifier.
I don't know quite what will happen in relationships with Date/Time as the
foreign key. It could certainly become a rpoblem in a multi-user database.
Although you can specify a sort field in a table for convenience, a table is
not for sorting data. That's where a query comes in.
Where does Numero come from? What are you trying to do with this database.
You can apply a filter based on the combo box selection, but there is too
little information to be specific about how. A subform would be based on
another table related to the first. Since you have only mentioned one table,
a subform is not a choice. It's hard to know if a continuous form is a good
choice without knowing what you are trying to do.

 

[Samora]

Hello Bruce

What i am trying to do is to have the reports of a medicin
doctor.
Something like that:

your are patient NUMBER 4 and you had been consulted a lot
of times (this is where the date comes in) and a report
(Memo Field).

So, what i want to do is enter in my PC , pick a patient
number (Number 4) and see all your consults with me by date.

Patient nr: 4

12-01-2005 wwwwwwwwwwwwwwwwwwwwwwwwwww
24-02-2005 sssssssssssssssssssssssssssss


i dont know if i made myself clear.. anyway if there is
anything else you need to help me, i would appreciate very
much.

i agree with you. Not a subform..

Waiting your reply

Best regards

Thanks in advance

 

[Guest]

You will need at least two tables, a patients table (tblPatients) and a
consultation table (tblConsult). The primary key from tblPatients will be
the foreign key from tblConsult. tblConsult will also have its own primary
key. You establish the primary key (autonumber data type is probably the
easiest) in table design view, and you establish the foreign key by
establishing a relationship between the primary key and the foreign key. Go
to Tools > Relationships, add both tables, and drag the key field from
tblPatients to tblConsult. See Help for more information.

tblPatients
PatientID (primary key)
LastName
FirstName
etc.

tblConsult
ConsultID (primary key)
PatientIID (foreign key, data type Number)
ConsultDate
etc.

This is one possibility for basic table structure. You would be doing
yourself a favor if you set it up so you can select the patient name rather
than their ID number. You can use the combo box wizard to help you with
this. Choose the option to Select a record based on the value from the combo
box (or something like that).
With this table structure, a subform is probably exactly what you need.
Establish the relationship between the tables, then try using Autoform to
make a form based on tblPatient. A subform for consultation should appear.
Another possibility is to make a form based on each table. Make the
autoform as above, delete the subform control, and in form design view drag
the icon for the form based on tblConsult onto the Patient form.
After you get the basic structure to work is when you would add the combo
box. One more thing is that you probably want to add and edit patient
information separately from the consultation record-keeping, in which case
you would probably want another form based on tblPatient for the exclusive
purpose of adding and changing patient information. There is a lot of stuff
here, so just try getting the basics to work, then post additional specific
questions.

 

[Samora]

Good evening Bruce

My tables are like you said:
i have a table TblPatients (TDoentes) and a table of
consults (TblConsultas - i have allready changed the keys -
Primary key is ConsultId as you said - autonumber and a
second key DoenteID - number field as foreign key allowing
duplicates because there will be a lot of consults for each
patient)

TDoentes has allready your structure
TConsultas i changed as you said.

i think i made the changes correctly on what concernes the
primary and the foreign key. Both fields have the key sign
on the left and ConsultID dont allow duplicates but Patient
ID allow duplicates on index.

i have allready a form for editing and changing my table of
patients. This was allready done.

my first question to you is:

when you mention that "i would do a favour to me if i can
select the patient by the name instead of the number, can i
assume that you want me to do a query based on TDoentes
ordered by name instead of number so the combo box looks
for the name instead of the number?

the relationships are allready done as you said too.
primary key of TblPatients to foreign key of TblConsults.

where can i find the autoform?????

I think this is the next step so i can take my work to a
next step.

i will see if i take a better look on the autoform issue,
so if i see that i can go any forward and then ask for any
more doubts.

Please forgive me all this stuff i am giving to you and
accept my best regards

Samora

 

[Guest]

The reason I suggest autoform and the various wizards is that it will let you
quickly see if things work as you would like. I almost always make my own
form for the finished database, but autoform lets me experiment quickly.
Insert > Autoform is one way, or it should be a button on the database
toolbar.
For the combo box, you could base it on TDoentes or on a separate query. In
either case you will be using data from TDoentes. TDoentes has a primary
key, and TblConsultas has a foreign key linked to the primary key in
TDoentes. You set up this link when you established the relationship between
the two tables. TblConsultas stores the primary key (number) for that
patient. When you need to see that patient's name you tell the database to
find the patient whose name is associated with that unique primary key. The
primary key remains the same even if the patient's name changes. If you make
a combo box based on TDoentes it will consist of two columns: 1) the
patient's ID (primary key), and 2) the patient's name. Access identifies the
record by the primary key. If it used the name there would be a problem if
two patients have the same name, and it would also not recognize that the
same patient can have different names (such as before and after being
married). The primary key column is the bound column (the one Access uses),
and the user sees the second column (the name). You can have as many columns
as you want. The width of the first colum is 0, and the width of the second
column is whatever you need. If you use the combo box wizard to make a combo
box to select a record, the wizard can take care of a lot of these details
for you.
Not to confuse you, but let me say that it would make things easier if you
make a query based on TDoentes. A query will let you do things like sort
names in alphabetical order, and combine first and last name into a single
field by putting something like this into a new column in query design view:
FullName: [FirstName] & " " & [LastName]. Once you have made the query you
can just base the form on it, just as you did with the form based on
TDoentes. If you have already made the form you can just change its Record
Source from TDoentes to the new query. As long as the query contains all the
fields you need it will be OK.