Select from List Box

[Franko]

Have a list of names & Phone# in list box on Form1, want a macro to copy
selected names and paste to a table. Table is a subform in Form1(no
relationship). All field names and format are similar)

I have
the form is 'Form1'
the list box is SelectNames
the table is ListOfNames(subform name is fsubListOfNames)

Macro -- (On Dbl Click)

GoToControl -- fsubListOfNames
GoToRecord -- New

SetValue -- Item -- [Forms]![Form1]![fsubListOfNames]
Expression -- [Forms]![Form1]![SelectNames]

I step through the macro, and ok until SetValue, then error message. Please
help.

 

[Steve Schapel]

Franko,

In the Item argument, you will need to include reference to the specific
textbox within the fsubListOfNames subform where you want the
SelectNames value entered. So, something like this...
[Forms]![Form1]![fsubListOfNames].[Form]![ClientName]

However, if your macro is being called from an event within the Form1
form, then you don't need to reference the parent form. So the macro
arguments would be more like this...
Item: [fsubListOfNames].[Form]![ClientName]
Expression: [SelectNames]

Having said that, what you are doing here is very unusual, and possibly
indicates that your table design could benefit form revision.

 

[Franko]

thank your for responding, makes sense and i will give it a try.

This is unusual and i keep thinking i am going about this db the wrong way.
The tables i mentioned were renamed for simplicity. The actual database is
medical and designed to add a new procedure at a hospital (such as Liver
Biopsy), then select billing codes (including values) from a list box, so
that we will know which codes we can bill for when this Liver Biopsy is
performed. Is there an easier way to select many codes from a list and paste
to a table, that a can build a report on?

Franko,

In the Item argument, you will need to include reference to the specific
textbox within the fsubListOfNames subform where you want the
SelectNames value entered. So, something like this...
[Forms]![Form1]![fsubListOfNames].[Form]![ClientName]

However, if your macro is being called from an event within the Form1
form, then you don't need to reference the parent form. So the macro
arguments would be more like this...
Item: [fsubListOfNames].[Form]![ClientName]
Expression: [SelectNames]

Having said that, what you are doing here is very unusual, and possibly
indicates that your table design could benefit form revision.

--
Steve Schapel, Microsoft Access MVP

Have a list of names & Phone# in list box on Form1, want a macro to copy
selected names and paste to a table. Table is a subform in Form1(no
relationship). All field names and format are similar)

I have
the form is 'Form1'
the list box is SelectNames
the table is ListOfNames(subform name is fsubListOfNames)

Macro -- (On Dbl Click)

GoToControl -- fsubListOfNames
GoToRecord -- New

SetValue -- Item -- [Forms]![Form1]![fsubListOfNames]
Expression -- [Forms]![Form1]![SelectNames]

I step through the macro, and ok until SetValue, then error message. Please
help.

 

[Steve Schapel]

Franko,

Normally, you would need 3 tables, something like this:

Procedures
ProcedureID
ProcedureName
etc
This is a "master list" of all procedures

BillingCodes
BillingCode
Description
StandardCharge
etc
This is a "master list" of all codes

ProcedureCodes
ProcedureCodeID
ProcedureID
BillingCode
This shows the combination of procedures and codes, i.e. which billing
codes are applicable to which procedures.

Data entry interface would almost certainly include a form based on the
Procedures table, with a continuous view subform based on the
ProcedureCodes table. This would allow the functionality that you
mentioned, i.e. the entry of a new procedure, along with all applicable
billing codes for that procedure.

In a nutshell... hope it makes sense.

--
Steve Schapel, Microsoft Access MVP

thank your for responding, makes sense and i will give it a try.

This is unusual and i keep thinking i am going about this db the wrong way.
The tables i mentioned were renamed for simplicity. The actual database is
medical and designed to add a new procedure at a hospital (such as Liver
Biopsy), then select billing codes (including values) from a list box, so
that we will know which codes we can bill for when this Liver Biopsy is
performed. Is there an easier way to select many codes from a list and paste
to a table, that a can build a report on?

Franko,

In the Item argument, you will need to include reference to the specific
textbox within the fsubListOfNames subform where you want the
SelectNames value entered. So, something like this...
[Forms]![Form1]![fsubListOfNames].[Form]![ClientName]

However, if your macro is being called from an event within the Form1
form, then you don't need to reference the parent form. So the macro
arguments would be more like this...
Item: [fsubListOfNames].[Form]![ClientName]
Expression: [SelectNames]

Having said that, what you are doing here is very unusual, and possibly
indicates that your table design could benefit form revision.

--
Steve Schapel, Microsoft Access MVP

Have a list of names & Phone# in list box on Form1, want a macro to copy
selected names and paste to a table. Table is a subform in Form1(no
relationship). All field names and format are similar)

I have
the form is 'Form1'
the list box is SelectNames
the table is ListOfNames(subform name is fsubListOfNames)

Macro -- (On Dbl Click)

GoToControl -- fsubListOfNames
GoToRecord -- New

SetValue -- Item -- [Forms]![Form1]![fsubListOfNames]
Expression -- [Forms]![Form1]![SelectNames]

I step through the macro, and ok until SetValue, then error message. Please
help.

 

[Franko]

Yes that does make sense, something i have tried, but maybe did not setup
correctly. In the subform I a datasheet view, with a drop down for the first
field, but could not get additional fields (such as description, value..), I
will give it a try, would be simpler than the method i am trying. Thanks alot
for the help.
Francis Debelo

Franko,

Normally, you would need 3 tables, something like this:

Procedures
ProcedureID
ProcedureName
etc
This is a "master list" of all procedures

BillingCodes
BillingCode
Description
StandardCharge
etc
This is a "master list" of all codes

ProcedureCodes
ProcedureCodeID
ProcedureID
BillingCode
This shows the combination of procedures and codes, i.e. which billing
codes are applicable to which procedures.

Data entry interface would almost certainly include a form based on the
Procedures table, with a continuous view subform based on the
ProcedureCodes table. This would allow the functionality that you
mentioned, i.e. the entry of a new procedure, along with all applicable
billing codes for that procedure.

In a nutshell... hope it makes sense.

--
Steve Schapel, Microsoft Access MVP

thank your for responding, makes sense and i will give it a try.

This is unusual and i keep thinking i am going about this db the wrong way.
The tables i mentioned were renamed for simplicity. The actual database is
medical and designed to add a new procedure at a hospital (such as Liver
Biopsy), then select billing codes (including values) from a list box, so
that we will know which codes we can bill for when this Liver Biopsy is
performed. Is there an easier way to select many codes from a list and paste
to a table, that a can build a report on?

Franko,

In the Item argument, you will need to include reference to the specific
textbox within the fsubListOfNames subform where you want the
SelectNames value entered. So, something like this...
[Forms]![Form1]![fsubListOfNames].[Form]![ClientName]

However, if your macro is being called from an event within the Form1
form, then you don't need to reference the parent form. So the macro
arguments would be more like this...
Item: [fsubListOfNames].[Form]![ClientName]
Expression: [SelectNames]

Having said that, what you are doing here is very unusual, and possibly
indicates that your table design could benefit form revision.

--
Steve Schapel, Microsoft Access MVP


Franko wrote:
Have a list of names & Phone# in list box on Form1, want a macro to copy
selected names and paste to a table. Table is a subform in Form1(no
relationship). All field names and format are similar)

I have
the form is 'Form1'
the list box is SelectNames
the table is ListOfNames(subform name is fsubListOfNames)

Macro -- (On Dbl Click)

GoToControl -- fsubListOfNames
GoToRecord -- New

SetValue -- Item -- [Forms]![Form1]![fsubListOfNames]
Expression -- [Forms]![Form1]![SelectNames]

I step through the macro, and ok until SetValue, then error message. Please
help.