Office 2003 Projects, .ADP

[Guest]

I created an Access database, .MDB, and have converted it to a Project file,
..ADP. I am having troubles with Combo Boxes. Is there a good source I could
refer to for MS Access 2003 Projects in general?

Specifically, regarding the Combo Boxes, in Access, the info in my combo box
was automatically displayed in the various fields on my form. Once I
converted to a Project file, the fields on my form are not being completed
automatically. I have tried using various VBA code with no luck.

 

[Arvin Meyer [MVP]]

Check your table names and make sure that you rename any (in the ADP) that
use the dbo.tblName or user.tablename format.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[aaron.kempf]

more importantly

post some code so we can help you

it's not just as straight forward as it sounds; there are some
functions in mdb that need to be translated into sql server syntax

 

[Guest]

Thanks for your help. Can you give me an example of what you mean?

Here is a brief description of the problem:
I have a combo box listing: PID,LastName,FirstName. When I make a
selection, the PID box is filled on my form, but the LastName box and
FirstName box on my form are still empty.

To get the form to fill in the LastName and FirstName for me, I created an
AfterUpdate Event with the below code:
me.lastname=me![pid].column(1)
me.firstname=me![pid].column(2)

The code worked fine; however, I get the following message when I try to
move on to the next record.

Write Conflict:
This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to make
changes.

Again, Thanks for your help. I am new to all of this, so I am probably
doing something stupid. The above lines are the first VBA code I have ever
used.

 

[Sylvain Lafontaine]

Probably a case of missing primary key: often (always?), the upsizing wizard
doesn't transfer them and you must explicitely reassign them. Check all the
other tables, their structures and the relations, too, to make sure that
everything is OK.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thanks for your help. Can you give me an example of what you mean?

Here is a brief description of the problem:
I have a combo box listing: PID,LastName,FirstName. When I make a
selection, the PID box is filled on my form, but the LastName box and
FirstName box on my form are still empty.

To get the form to fill in the LastName and FirstName for me, I created
an
AfterUpdate Event with the below code:
me.lastname=me![pid].column(1)
me.firstname=me![pid].column(2)

The code worked fine; however, I get the following message when I try to
move on to the next record.

Write Conflict:
This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user
made.
Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to
make
changes.

Again, Thanks for your help. I am new to all of this, so I am probably
doing something stupid. The above lines are the first VBA code I have
ever
used.

Check your table names and make sure that you rename any (in the ADP)
that
use the dbo.tblName or user.tablename format.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

All looks well. This is not the problem.

I decided to create two identical test databases. One database .mdb the
other .adp. Why is it that with .mdb a combo box selection will auto fill
form fields; however, with a .adp a combo box will NOT auto fill form fields?

How do I create a combo box in a .adp database (Access Project) to autofill
the form?

Thanks ... Any help greatly appreciated ....

Probably a case of missing primary key: often (always?), the upsizing wizard
doesn't transfer them and you must explicitely reassign them. Check all the
other tables, their structures and the relations, too, to make sure that
everything is OK.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thanks for your help. Can you give me an example of what you mean?

Here is a brief description of the problem:
I have a combo box listing: PID,LastName,FirstName. When I make a
selection, the PID box is filled on my form, but the LastName box and
FirstName box on my form are still empty.

To get the form to fill in the LastName and FirstName for me, I created
an
AfterUpdate Event with the below code:
me.lastname=me![pid].column(1)
me.firstname=me![pid].column(2)

The code worked fine; however, I get the following message when I try to
move on to the next record.

Write Conflict:
This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user
made.
Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to
make
changes.

Again, Thanks for your help. I am new to all of this, so I am probably
doing something stupid. The above lines are the first VBA code I have
ever
used.

Check your table names and make sure that you rename any (in the ADP)
that
use the dbo.tblName or user.tablename format.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I created an Access database, .MDB, and have converted it to a Project
file,
.ADP. I am having troubles with Combo Boxes. Is there a good source I
could
refer to for MS Access 2003 Projects in general?

Specifically, regarding the Combo Boxes, in Access, the info in my
combo
box
was automatically displayed in the various fields on my form. Once I
converted to a Project file, the fields on my form are not being
completed
automatically. I have tried using various VBA code with no luck.

 

[Sylvain Lafontaine]

By autofill, you mean using code such as « me.lastname=me![pid].column(1) »
in the AfterUpdate event of the combobox?

There is no difference between MDB and ADP for the combobox on this point.
However, if there is no bug in your code, you should be able to see the new
values of LastName and FirstName after changing the selected value of the
combobox and before moving to another record.

If changing the selected value of the combobox doesn't have any effect on
the displayed values, then there is a problem at this level. You should put
a MsgBox in the AfterUpdate event to check the values of .Column(1) and
..Column(2) to make sure that they contains the right values. BTW, are you
seeing these values when you are using the combobox? (If not, then check
its ColumnCount propertie).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

All looks well. This is not the problem.

I decided to create two identical test databases. One database .mdb the
other .adp. Why is it that with .mdb a combo box selection will auto fill
form fields; however, with a .adp a combo box will NOT auto fill form
fields?

How do I create a combo box in a .adp database (Access Project) to
autofill
the form?

Thanks ... Any help greatly appreciated ....

Probably a case of missing primary key: often (always?), the upsizing
wizard
doesn't transfer them and you must explicitely reassign them. Check all
the
other tables, their structures and the relations, too, to make sure that
everything is OK.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thanks for your help. Can you give me an example of what you mean?

Here is a brief description of the problem:
I have a combo box listing: PID,LastName,FirstName. When I make a
selection, the PID box is filled on my form, but the LastName box and
FirstName box on my form are still empty.

To get the form to fill in the LastName and FirstName for me, I
created
an
AfterUpdate Event with the below code:
me.lastname=me![pid].column(1)
me.firstname=me![pid].column(2)

The code worked fine; however, I get the following message when I try
to
move on to the next record.

Write Conflict:
This record has been changed by another user since you started editing
it.
If you save the record, you will overwrite the changes the other user
made.
Copying the changes to the clipboard will let you look at the values
the
other user entered, and then paste your changes back in if you decide
to
make
changes.

Again, Thanks for your help. I am new to all of this, so I am probably
doing something stupid. The above lines are the first VBA code I have
ever
used.

:

Check your table names and make sure that you rename any (in the ADP)
that
use the dbo.tblName or user.tablename format.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I created an Access database, .MDB, and have converted it to a
Project
file,
.ADP. I am having troubles with Combo Boxes. Is there a good
source I
could
refer to for MS Access 2003 Projects in general?

Specifically, regarding the Combo Boxes, in Access, the info in my
combo
box
was automatically displayed in the various fields on my form. Once
I
converted to a Project file, the fields on my form are not being
completed
automatically. I have tried using various VBA code with no luck.

 

[Guest]

Keep in mind that NEITHER of the two test databases I created had code, one
being the .mdb and the other being .adp. Thats correct, NO code at all in
the two test databases.

The access database, .mdb, just figured it all out. I clicked on the combo
box, selected the person I wanted (from the info displayed in the combo box,
ie. ID, first name, last name) and all the fields were updated on the form
instantly, i.e. ID, first name, last name.

The access project, .adp, is another story. I click on the combo box and
see all the available ID's and names within the combo box, but when I click
on one of the combo box entries, only the ID field is filled in on the form.
The persons first name and last name are left blank. Now, if I process the
record and go back to view the record, I do see all the info on the form, i.e
ID, first name, last name. Don't want that though. Want to see it
instantly.

The one time I added code to the .adp, I did see the ID, first name, and
last name instantly, but then I got that error, I stated in an earlier
message, when I tried to process the record.

Thanks again for all your help. It looks to me that Access databases and
Access Projects are very different when it comes to handling combo boxes. I
am just learning VBA. I bought a big book. That is going to take a lot of
time to go through. It looks like I will have to learn VBA to do combo
boxes. I may have to design the form with an alternative method in mind, i.e
no combo boxes. I would really like to get this figured out though.

Do you know of any good books on Access Projects?

Thanks

By autofill, you mean using code such as « me.lastname=me![pid].column(1) »
in the AfterUpdate event of the combobox?

There is no difference between MDB and ADP for the combobox on this point.
However, if there is no bug in your code, you should be able to see the new
values of LastName and FirstName after changing the selected value of the
combobox and before moving to another record.

If changing the selected value of the combobox doesn't have any effect on
the displayed values, then there is a problem at this level. You should put
a MsgBox in the AfterUpdate event to check the values of .Column(1) and
..Column(2) to make sure that they contains the right values. BTW, are you
seeing these values when you are using the combobox? (If not, then check
its ColumnCount propertie).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

All looks well. This is not the problem.

I decided to create two identical test databases. One database .mdb the
other .adp. Why is it that with .mdb a combo box selection will auto fill
form fields; however, with a .adp a combo box will NOT auto fill form
fields?

How do I create a combo box in a .adp database (Access Project) to
autofill
the form?

Thanks ... Any help greatly appreciated ....

Probably a case of missing primary key: often (always?), the upsizing
wizard
doesn't transfer them and you must explicitely reassign them. Check all
the
other tables, their structures and the relations, too, to make sure that
everything is OK.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Thanks for your help. Can you give me an example of what you mean?

Here is a brief description of the problem:
I have a combo box listing: PID,LastName,FirstName. When I make a
selection, the PID box is filled on my form, but the LastName box and
FirstName box on my form are still empty.

To get the form to fill in the LastName and FirstName for me, I
created
an
AfterUpdate Event with the below code:
me.lastname=me![pid].column(1)
me.firstname=me![pid].column(2)

The code worked fine; however, I get the following message when I try
to
move on to the next record.

Write Conflict:
This record has been changed by another user since you started editing
it.
If you save the record, you will overwrite the changes the other user
made.
Copying the changes to the clipboard will let you look at the values
the
other user entered, and then paste your changes back in if you decide
to
make
changes.

Again, Thanks for your help. I am new to all of this, so I am probably
doing something stupid. The above lines are the first VBA code I have
ever
used.

:

Check your table names and make sure that you rename any (in the ADP)
that
use the dbo.tblName or user.tablename format.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I created an Access database, .MDB, and have converted it to a
Project
file,
.ADP. I am having troubles with Combo Boxes. Is there a good
source I
could
refer to for MS Access 2003 Projects in general?

Specifically, regarding the Combo Boxes, in Access, the info in my
combo
box
was automatically displayed in the various fields on my form. Once
I
converted to a Project file, the fields on my form are not being
completed
automatically. I have tried using various VBA code with no luck.

 

[Sylvain Lafontaine]

Oh, you mean that you are using the AutoLookup facility of Access? I don't
use it personally with ADP because I'm using Stored Procedures to retrieve
my data; however, by using Views, you shouldn't have any problem with this
feature inside an ADP project for as far as I know. (However, I might be
wrong on this point; I will try to make some tests later.)

When you upsized your database to SQL-Server, did you choose the DRI (Data
Referential Integrity) or the Triggers option for your relations? If you
choose the Triggers option, then it's normal that the AutoLookup facility
don't work.

For books, the only one that I know is "Microsoft Access Projects with
Microsoft SQL Server", Ralf Albrecht and Natascha Nicol, Microsoft Press,
2002.

Some other books like "Special Edition Using Microsoft Access 2002" will
also talk about ADP but their coverage is limited.

BTW, if you don't know about VBA, then you will have a hard time with ADP
and SQL-Server. The fact is that the combination ADP/SQL-Server is not as
easy as working with a MDB file only. In your case, a better option might
be to use a MDB file with linked tables to SQL-Server. See
http://support.microsoft.com/kb/q209123/ to have updatable Views with this
later combination.

Finally, there is a newsgroup about ADP:
microsoft.public.access.adp.sqlserver.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Keep in mind that NEITHER of the two test databases I created had code,
one
being the .mdb and the other being .adp. Thats correct, NO code at all in
the two test databases.

The access database, .mdb, just figured it all out. I clicked on the
combo
box, selected the person I wanted (from the info displayed in the combo
box,
ie. ID, first name, last name) and all the fields were updated on the form
instantly, i.e. ID, first name, last name.

The access project, .adp, is another story. I click on the combo box and
see all the available ID's and names within the combo box, but when I
click
on one of the combo box entries, only the ID field is filled in on the
form.
The persons first name and last name are left blank. Now, if I process
the
record and go back to view the record, I do see all the info on the form,
i.e
ID, first name, last name. Don't want that though. Want to see it
instantly.

The one time I added code to the .adp, I did see the ID, first name, and
last name instantly, but then I got that error, I stated in an earlier
message, when I tried to process the record.

Thanks again for all your help. It looks to me that Access databases and
Access Projects are very different when it comes to handling combo boxes.
I
am just learning VBA. I bought a big book. That is going to take a lot
of
time to go through. It looks like I will have to learn VBA to do combo
boxes. I may have to design the form with an alternative method in mind,
i.e
no combo boxes. I would really like to get this figured out though.

Do you know of any good books on Access Projects?

Thanks

By autofill, you mean using code such as «
me.lastname=me![pid].column(1) »
in the AfterUpdate event of the combobox?

There is no difference between MDB and ADP for the combobox on this
point.
However, if there is no bug in your code, you should be able to see the
new
values of LastName and FirstName after changing the selected value of the
combobox and before moving to another record.

If changing the selected value of the combobox doesn't have any effect on
the displayed values, then there is a problem at this level. You should
put
a MsgBox in the AfterUpdate event to check the values of .Column(1) and
..Column(2) to make sure that they contains the right values. BTW, are
you
seeing these values when you are using the combobox? (If not, then check
its ColumnCount propertie).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

All looks well. This is not the problem.

I decided to create two identical test databases. One database .mdb
the
other .adp. Why is it that with .mdb a combo box selection will auto
fill
form fields; however, with a .adp a combo box will NOT auto fill form
fields?

How do I create a combo box in a .adp database (Access Project) to
autofill
the form?

Thanks ... Any help greatly appreciated ....

:

Probably a case of missing primary key: often (always?), the upsizing
wizard
doesn't transfer them and you must explicitely reassign them. Check
all
the
other tables, their structures and the relations, too, to make sure
that
everything is OK.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Thanks for your help. Can you give me an example of what you mean?

Here is a brief description of the problem:
I have a combo box listing: PID,LastName,FirstName. When I make a
selection, the PID box is filled on my form, but the LastName box
and
FirstName box on my form are still empty.

To get the form to fill in the LastName and FirstName for me, I
created
an
AfterUpdate Event with the below code:
me.lastname=me![pid].column(1)
me.firstname=me![pid].column(2)

The code worked fine; however, I get the following message when I
try
to
move on to the next record.

Write Conflict:
This record has been changed by another user since you started
editing
it.
If you save the record, you will overwrite the changes the other
user
made.
Copying the changes to the clipboard will let you look at the values
the
other user entered, and then paste your changes back in if you
decide
to
make
changes.

Again, Thanks for your help. I am new to all of this, so I am
probably
doing something stupid. The above lines are the first VBA code I
have
ever
used.

:

Check your table names and make sure that you rename any (in the
ADP)
that
use the dbo.tblName or user.tablename format.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I created an Access database, .MDB, and have converted it to a
Project
file,
.ADP. I am having troubles with Combo Boxes. Is there a good
source I
could
refer to for MS Access 2003 Projects in general?

Specifically, regarding the Combo Boxes, in Access, the info in
my
combo
box
was automatically displayed in the various fields on my form.
Once
I
converted to a Project file, the fields on my form are not being
completed
automatically. I have tried using various VBA code with no luck.

 

[aaron.kempf]

i use views a LOT in ADP
i mean-- you have to use views a lot if you use Analysis Services.

and if you're not using Analysis Services; then it's time to grow up
kids and learn something productive.. instead of screwing around with
kid's databases

 

[aaron.kempf]

and if you want my help; you're going to have to give a lot more
detail. i mean-- im clueless what you're talking about here


and just for the record; tihs Sylvain WUSS is the last person i'd
listen to; i mean-- he's a script-kiddie that's scared to learn SQL
Server.

ADP is just flat out more powerful than silly MDB-linked-ODBC bullshit