Writing data from a table to another

  • Thread starter Thread starter max
  • Start date Start date
M

max

Hello,
I'm working on an Access db in VS2005. I have a table, called tblPatients,
in which I have three columns: PatientID (primary key), PatientName and
DoctorName. This table is filled with more than 4.000 records. I load this
table on a form, I make queries, updates, deletion, it does work fine. Then
I have a second form, in which I need to display the two main columns from
tblPatients (PatientName and DoctorName), and some more elements: medical
visits performed (till 5 in the same day), date, fares for each visit, total
amount, and a discount field. The operator starts selecting the Patients
from a ComboBox, which displays automatically its DoctorsName field; it's
fine because the operator doesn't need to choose manually the doctors! This
form loads a second table, called Listing. The columns in this table are:
ListingID (primary key), PatientsID, DoctorsName, Date, Fare1... Fare5,
Total, Discount. I need all these elements are written in this second table.
Well, I have noted that almost all these elements are written, but the items
belonging to "DoctorName", coming from the previous table (tblPatients), are
not physically written in this table, just displayed (correctly), and the
items belonging to "PatientName" are written only in the "PatientID" form
(numbers). I have used a ComboBox for PatientsName, bounded to:
Data Source: tblPatientsBindingSource; Display member: PatientName; Value:
PatientID; Selected Value: ListingBindingSource - PatientID.
Then I have used a TextBox for DoctorName, bounded to:
TblPatientsBindingSource - DoctorName.
I need the items are written in this table in the explicit way (and actually
the DoctorName is not written at all), because then I need to let operator
to perform many queries on it, and he must selects the doctors and the
patients by name, not by an ID.
I didn't write any lines of code, all is written by designer.
Thanks

Max
 
Yes Robin, I have a question:
[CUT] I need the items are written in this table in the explicit way (and
actually the DoctorName is not written at all), because then I need to let
operator to perform many queries on it, and he must selects the doctors and
the patients by name, not by an ID.
How can I do this?

Thanks.
Max
 
Am I reading this right, that the problem is that the DoctorsName is not
stored in the [Listing] table ? Is that the problem you are having?

You need to bind the combobox to the TblPatientsBindingSource for display,
but you need to set the SelectedValue to ListingBindingSource/DoctorName.

If you have the Listing table in a data source, you can just drag the
[doctorname] from the listing table over and drop it on the combobox you
have already bound to the TblPatientsBindingSource, and it will do the
double-binding for you.

You can double-check this in the Code-Behind -- show all files (click the
icon in the Solution Explorer) and look at the myform.designer.vb code for
your combobox. It should have DisplayMember and ValueMember pointing at
TblPatientsBindingSource, but SelectedValue should point at
ListingBindingSource.

BTW, next time you post, please put some paragraphs in your message. Most
people won't read a 35-line paragraph. You'll get more help if you separate
it out better.

Thanks,
Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
max said:
Yes Robin, I have a question:
[CUT] I need the items are written in this table in the explicit way (and
actually the DoctorName is not written at all), because then I need to
let operator to perform many queries on it, and he must selects the
doctors and the patients by name, not by an ID.
How can I do this?

Thanks.
Max

RobinS said:
Did you have a question, or are you just sharing?

Robin S.
Click to expand...
Click to expand...
 
Hi,
it works, Robin, thanks.
However I'd like to use a textbox in place of a combobox, because this field
should not be editable by operator, and even if I disable it from properties
the down arrow remains visible and it's not so elegant.
How can I use the same bindings you suggested for a combobox in a textbox,
if applicable?
Thanks,
Max
RobinS said:
Am I reading this right, that the problem is that the DoctorsName is not
stored in the [Listing] table ? Is that the problem you are having?

You need to bind the combobox to the TblPatientsBindingSource for display,
but you need to set the SelectedValue to ListingBindingSource/DoctorName.

If you have the Listing table in a data source, you can just drag the
[doctorname] from the listing table over and drop it on the combobox you
have already bound to the TblPatientsBindingSource, and it will do the
double-binding for you.

You can double-check this in the Code-Behind -- show all files (click the
icon in the Solution Explorer) and look at the myform.designer.vb code for
your combobox. It should have DisplayMember and ValueMember pointing at
TblPatientsBindingSource, but SelectedValue should point at
ListingBindingSource.

BTW, next time you post, please put some paragraphs in your message. Most
people won't read a 35-line paragraph. You'll get more help if you
separate it out better.> Robin S.

Thanks,
Click to expand...
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
max said:
Yes Robin, I have a question:
[CUT] I need the items are written in this table in the explicit way (and
actually the DoctorName is not written at all), because then I need to
let operator to perform many queries on it, and he must selects the
doctors and the patients by name, not by an ID.
How can I do this?

Thanks.
Max

RobinS said:
Did you have a question, or are you just sharing?

Robin S.
----------------------------------------

Hello,
I'm working on an Access db in VS2005. I have a table, called
tblPatients, in which I have three columns: PatientID (primary key),
PatientName and DoctorName. This table is filled with more than 4.000
records. I load this table on a form, I make queries, updates,
deletion, it does work fine. Then I have a second form, in which I need
to display the two main columns from tblPatients (PatientName and
DoctorName), and some more elements: medical visits performed (till 5
in the same day), date, fares for each visit, total amount, and a
discount field. The operator starts selecting the Patients from a
ComboBox, which displays automatically its DoctorsName field; it's fine
because the operator doesn't need to choose manually the doctors! This
form loads a second table, called Listing. The columns in this table
are:
ListingID (primary key), PatientsID, DoctorsName, Date, Fare1... Fare5,
Total, Discount. I need all these elements are written in this second
table.
Well, I have noted that almost all these elements are written, but the
items belonging to "DoctorName", coming from the previous table
(tblPatients), are not physically written in this table, just displayed
(correctly), and the items belonging to "PatientName" are written only
in the "PatientID" form
(numbers). I have used a ComboBox for PatientsName, bounded to:
Data Source: tblPatientsBindingSource; Display member: PatientName;
Value: PatientID; Selected Value: ListingBindingSource - PatientID.
Then I have used a TextBox for DoctorName, bounded to:
TblPatientsBindingSource - DoctorName.
I need the items are written in this table in the explicit way (and
actually the DoctorName is not written at all), because then I need to
let operator
to perform many queries on it, and he must selects the doctors and the
patients by name, not by an ID.
I didn't write any lines of code, all is written by designer.
Thanks

Max
Click to expand...
Click to expand...
Click to expand...
 
Huh? You said the user was going to select the doctor out of the combobox.
What is it exactly you're trying to do now?

I can't bear to read this again and figure out what you're talking about.
Please restate the problem, and use paragraphs. If you post a 30-line
response with no breaks in it, I'm going back to my real job. ;-)

Robin S.
------------------------------
max said:
Hi,
it works, Robin, thanks.
However I'd like to use a textbox in place of a combobox, because this
field should not be editable by operator, and even if I disable it from
properties the down arrow remains visible and it's not so elegant.
How can I use the same bindings you suggested for a combobox in a
textbox, if applicable?
Thanks,
Max
RobinS said:
Am I reading this right, that the problem is that the DoctorsName is not
stored in the [Listing] table ? Is that the problem you are having?

You need to bind the combobox to the TblPatientsBindingSource for
display, but you need to set the SelectedValue to
ListingBindingSource/DoctorName.

If you have the Listing table in a data source, you can just drag the
[doctorname] from the listing table over and drop it on the combobox you
have already bound to the TblPatientsBindingSource, and it will do the
double-binding for you.

You can double-check this in the Code-Behind -- show all files (click
the icon in the Solution Explorer) and look at the myform.designer.vb
code for your combobox. It should have DisplayMember and ValueMember
pointing at TblPatientsBindingSource, but SelectedValue should point at
ListingBindingSource.

BTW, next time you post, please put some paragraphs in your message.
Most people won't read a 35-line paragraph. You'll get more help if you
separate it out better.> Robin S.

Thanks,
Click to expand...
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
max said:
Yes Robin, I have a question:
[CUT] I need the items are written in this table in the explicit way
(and actually the DoctorName is not written at all), because then I
need to let operator to perform many queries on it, and he must selects
the doctors and the patients by name, not by an ID.
How can I do this?

Thanks.
Max

"RobinS" <[email protected]> ha scritto nel messaggio

Did you have a question, or are you just sharing?

Robin S.
----------------------------------------

Hello,
I'm working on an Access db in VS2005. I have a table, called
tblPatients, in which I have three columns: PatientID (primary key),
PatientName and DoctorName. This table is filled with more than 4.000
records. I load this table on a form, I make queries, updates,
deletion, it does work fine. Then I have a second form, in which I
need to display the two main columns from tblPatients (PatientName
and DoctorName), and some more elements: medical visits performed
(till 5 in the same day), date, fares for each visit, total amount,
and a discount field. The operator starts selecting the Patients from
a ComboBox, which displays automatically its DoctorsName field; it's
fine because the operator doesn't need to choose manually the
doctors! This form loads a second table, called Listing. The columns
in this table are:
ListingID (primary key), PatientsID, DoctorsName, Date, Fare1...
Fare5, Total, Discount. I need all these elements are written in this
second table.
Well, I have noted that almost all these elements are written, but
the items belonging to "DoctorName", coming from the previous table
(tblPatients), are not physically written in this table, just
displayed (correctly), and the items belonging to "PatientName" are
written only in the "PatientID" form
(numbers). I have used a ComboBox for PatientsName, bounded to:
Data Source: tblPatientsBindingSource; Display member: PatientName;
Value: PatientID; Selected Value: ListingBindingSource - PatientID.
Then I have used a TextBox for DoctorName, bounded to:
TblPatientsBindingSource - DoctorName.
I need the items are written in this table in the explicit way (and
actually the DoctorName is not written at all), because then I need
to let operator
to perform many queries on it, and he must selects the doctors and
the patients by name, not by an ID.
I didn't write any lines of code, all is written by designer.
Thanks

Max
Click to expand...
Click to expand...
Click to expand...
 
Hi,
the DoctorName in this form (called Form_all) must not to be changed,
because it comes from another table, called Doctors.
But it must be displayed because it's linked to the Patient name, which
comes from one more table, called Patients.
So, choosing a patient from a combobox, the Doctor name comes automatically.
And because of I need to write all the datas from this form ("Form_all") to
a table called "Listing", I use the binding method you described me, and it
works.
But, because of DoctorName in this form must only be displayed, I'd like to
use a textbox in place of a combobox; I realize that this is only a display
issue (the dropdown arrow inputs operator to click on it, even setting to
"False" the enable property), but I'm curious to understand if a textbox can
be bounded as a combobox in some way.
Hope I was clear and I have used many paragraph. :)

RobinS said:
Huh? You said the user was going to select the doctor out of the combobox.
What is it exactly you're trying to do now?

I can't bear to read this again and figure out what you're talking about.
Please restate the problem, and use paragraphs. If you post a 30-line
response with no breaks in it, I'm going back to my real job. ;-)

Robin S.
------------------------------
max said:
Hi,
it works, Robin, thanks.
However I'd like to use a textbox in place of a combobox, because this
field should not be editable by operator, and even if I disable it from
properties the down arrow remains visible and it's not so elegant.
How can I use the same bindings you suggested for a combobox in a
textbox, if applicable?
Thanks,
Max
RobinS said:
Am I reading this right, that the problem is that the DoctorsName is not
stored in the [Listing] table ? Is that the problem you are having?

You need to bind the combobox to the TblPatientsBindingSource for
display, but you need to set the SelectedValue to
ListingBindingSource/DoctorName.

If you have the Listing table in a data source, you can just drag the
[doctorname] from the listing table over and drop it on the combobox you
have already bound to the TblPatientsBindingSource, and it will do the
double-binding for you.

You can double-check this in the Code-Behind -- show all files (click
the icon in the Solution Explorer) and look at the myform.designer.vb
code for your combobox. It should have DisplayMember and ValueMember
pointing at TblPatientsBindingSource, but SelectedValue should point at
ListingBindingSource.

BTW, next time you post, please put some paragraphs in your message.
Most people won't read a 35-line paragraph. You'll get more help if you
separate it out better.> Robin S.

Thanks,
Click to expand...
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------

Yes Robin, I have a question:
[CUT] I need the items are written in this table in the explicit way
(and actually the DoctorName is not written at all), because then I
need to let operator to perform many queries on it, and he must selects
the doctors and the patients by name, not by an ID.
How can I do this?

Thanks.
Max

"RobinS" <[email protected]> ha scritto nel messaggio

Did you have a question, or are you just sharing?

Robin S.
----------------------------------------

Hello,
I'm working on an Access db in VS2005. I have a table, called
tblPatients, in which I have three columns: PatientID (primary key),
PatientName and DoctorName. This table is filled with more than 4.000
records. I load this table on a form, I make queries, updates,
deletion, it does work fine. Then I have a second form, in which I
need to display the two main columns from tblPatients (PatientName
and DoctorName), and some more elements: medical visits performed
(till 5 in the same day), date, fares for each visit, total amount,
and a discount field. The operator starts selecting the Patients from
a ComboBox, which displays automatically its DoctorsName field; it's
fine because the operator doesn't need to choose manually the
doctors! This form loads a second table, called Listing. The columns
in this table are:
ListingID (primary key), PatientsID, DoctorsName, Date, Fare1...
Fare5, Total, Discount. I need all these elements are written in this
second table.
Well, I have noted that almost all these elements are written, but
the items belonging to "DoctorName", coming from the previous table
(tblPatients), are not physically written in this table, just
displayed (correctly), and the items belonging to "PatientName" are
written only in the "PatientID" form
(numbers). I have used a ComboBox for PatientsName, bounded to:
Data Source: tblPatientsBindingSource; Display member: PatientName;
Value: PatientID; Selected Value: ListingBindingSource - PatientID.
Then I have used a TextBox for DoctorName, bounded to:
TblPatientsBindingSource - DoctorName.
I need the items are written in this table in the explicit way (and
actually the DoctorName is not written at all), because then I need
to let operator
to perform many queries on it, and he must selects the doctors and
the patients by name, not by an ID.
I didn't write any lines of code, all is written by designer.
Thanks

Max
Click to expand...
Click to expand...
Click to expand...
 
Okay, I've thought about this for a bit.

The short answer is "No." You can't bind a textbox the same way.

If you want to show the associated [doctorname] in a textbox, however,
there are multiple ways to do that, and each has their positives and
negatives.

If you were using business objects, you could add a property to your class
for doctorname and fill it when you populate everything else. But you're
not, are you?

You could change the query that you use to populate the displayed
information to join with the other table and pick up the doctorname. If you
do this, you have to write your own update queries so it will exclude that
field. I'm not sure how you're updating, but it's probably generated code,
so that won't work. Aren't you using strongly typed datasets?

You could load that field every time they change the record being
displayed. Just go get the doctor name and fill in the textbox. This is
quick and dirty and would work. You could set up a strongly typed dataset
that goes to the table with patient and doctor in it and pulls the doctor
name for a patient that you pass in as a parameter. In this code, you would
run the Get method and then stick the doctor name in the textbox. If you're
not using strongly typed datasets, you could just write your own query to
do that and go get it.

If the form you want to display [doctorname] on is being displayed by
another form that actually has access to that information, you can pass it
in when you instantiate the form and stick it in the textbox. I'm not sure
how your app is working. If you are having them select stuff, and then they
bring up this last form to update information, and then they exit the form
and save the data and return to the calling form, this is the best way to
do it. Just add a constructor to the form that takes a string and stick it
in the textbox. Don't forget if you decide to do this, you need to also add
a constructor with no parameters if you *ever* want to create the form that
way. This is the default constructor; it exists behind the scenes if you
have no constructors, but the minute you add a constructor, you have to add
your own default one too.

Public Sub New()
InitializeComponent()
End Sub
Public Sub New(docName As String)
InitializeComponent()
txtDoctorName.Text = docName
End Sub

If anybody else has some ideas, please feel free to chip in.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
max said:
Hi,
the DoctorName in this form (called Form_all) must not to be changed,
because it comes from another table, called Doctors.
But it must be displayed because it's linked to the Patient name, which
comes from one more table, called Patients.
So, choosing a patient from a combobox, the Doctor name comes
automatically.
And because of I need to write all the datas from this form ("Form_all")
to a table called "Listing", I use the binding method you described me,
and it works.
But, because of DoctorName in this form must only be displayed, I'd like
to use a textbox in place of a combobox; I realize that this is only a
display issue (the dropdown arrow inputs operator to click on it, even
setting to "False" the enable property), but I'm curious to understand if
a textbox can be bounded as a combobox in some way.
Hope I was clear and I have used many paragraph. :)

RobinS said:
Huh? You said the user was going to select the doctor out of the
combobox. What is it exactly you're trying to do now?

I can't bear to read this again and figure out what you're talking
about. Please restate the problem, and use paragraphs. If you post a
30-line response with no breaks in it, I'm going back to my real job.
;-)

Robin S.
------------------------------
max said:
Hi,
it works, Robin, thanks.
However I'd like to use a textbox in place of a combobox, because this
field should not be editable by operator, and even if I disable it from
properties the down arrow remains visible and it's not so elegant.
How can I use the same bindings you suggested for a combobox in a
textbox, if applicable?
Thanks,
Max
"RobinS" <[email protected]> ha scritto nel messaggio

Am I reading this right, that the problem is that the DoctorsName is
not stored in the [Listing] table ? Is that the problem you are
having?

You need to bind the combobox to the TblPatientsBindingSource for
display, but you need to set the SelectedValue to
ListingBindingSource/DoctorName.

If you have the Listing table in a data source, you can just drag the
[doctorname] from the listing table over and drop it on the combobox
you have already bound to the TblPatientsBindingSource, and it will do
the double-binding for you.

You can double-check this in the Code-Behind -- show all files (click
the icon in the Solution Explorer) and look at the myform.designer.vb
code for your combobox. It should have DisplayMember and ValueMember
pointing at TblPatientsBindingSource, but SelectedValue should point
at ListingBindingSource.

BTW, next time you post, please put some paragraphs in your message.
Most people won't read a 35-line paragraph. You'll get more help if
you separate it out better.> Robin S.

Thanks,

Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------

Yes Robin, I have a question:
[CUT] I need the items are written in this table in the explicit way
(and actually the DoctorName is not written at all), because then I
need to let operator to perform many queries on it, and he must
selects the doctors and the patients by name, not by an ID.
How can I do this?

Thanks.
Max

"RobinS" <[email protected]> ha scritto nel messaggio

Did you have a question, or are you just sharing?

Robin S.
----------------------------------------

Hello,
I'm working on an Access db in VS2005. I have a table, called
tblPatients, in which I have three columns: PatientID (primary
key), PatientName and DoctorName. This table is filled with more
than 4.000 records. I load this table on a form, I make queries,
updates, deletion, it does work fine. Then I have a second form, in
which I need to display the two main columns from tblPatients
(PatientName and DoctorName), and some more elements: medical
visits performed (till 5 in the same day), date, fares for each
visit, total amount, and a discount field. The operator starts
selecting the Patients from a ComboBox, which displays
automatically its DoctorsName field; it's fine because the operator
doesn't need to choose manually the doctors! This form loads a
second table, called Listing. The columns in this table are:
ListingID (primary key), PatientsID, DoctorsName, Date, Fare1...
Fare5, Total, Discount. I need all these elements are written in
this second table.
Well, I have noted that almost all these elements are written, but
the items belonging to "DoctorName", coming from the previous table
(tblPatients), are not physically written in this table, just
displayed (correctly), and the items belonging to "PatientName" are
written only in the "PatientID" form
(numbers). I have used a ComboBox for PatientsName, bounded to:
Data Source: tblPatientsBindingSource; Display member: PatientName;
Value: PatientID; Selected Value: ListingBindingSource - PatientID.
Then I have used a TextBox for DoctorName, bounded to:
TblPatientsBindingSource - DoctorName.
I need the items are written in this table in the explicit way (and
actually the DoctorName is not written at all), because then I need
to let operator
to perform many queries on it, and he must selects the doctors and
the patients by name, not by an ID.
I didn't write any lines of code, all is written by designer.
Thanks

Max
Click to expand...
Click to expand...
Click to expand...
 
Back
Top