By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

HELP! - NEED VB CODE FOR BEGINNER

P: n/a
Hi,

I have a printer consumables database.
I have a form designed to change the status of consumables ie. in use,
dead, stock etc.

I select one of my consumables records from the combo box and it
displays the current 'status' field in a 2nd combo box.
I can then select from the status table (ie in use, dead etc) but i
need to take that data and re-insert it back into the consumable
record.

My variables are so:
form name: amend consumables status
consumable table name: dbo_consumables
status field: dbo_consumables.status
combo box (2nd) name: status

I have a tick button below this to take the entry in combo box
'status' for 'onclick' and enter it into the table but i cant get the
code right.

I've tried variations like:

Me!dbo_consumables.STATUS = Forms![amend consumables status]![STATUS]

Me![STATUS] = Forms![amend consumables status]![STATUS]

SELECT tbldbo_consumables.status = Me![STATUS]

and many more but to no available...

most of the time it does not recognise dbo_consumables table...i think
its the syntax i am using.

Any suggestions would be very helpful as this is the last task to
complete my database.

Mark
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"SELECT whatever = somevalue" is not valid SQL for updating. Given some of
the names you use (starting with dbo_), I suspect you are working with SQL
Server. If you really need to execute SQL, you'll need to either link the
table via ODBC and create Jet SQL to update or create the proper SQL Server
SQL and execute it as a Passthrough Query (or have someone else explain how
to do it in ADO if you aren't connected to SQL Server via ODBC).

If the Form is bound to the record where you want to store the value, and
the Combo is bound to the field where you want to store it, simply selecting
it in the Combo will cause it to be stored in the underlying record.

Perhaps I am missing something here. If so, please clarify.

Larry Linson
Microsoft Access MVP

"MARK" <ba******@hotmail.com> wrote in message
news:be**************************@posting.google.c om...
Hi,

I have a printer consumables database.
I have a form designed to change the status of consumables ie. in use,
dead, stock etc.

I select one of my consumables records from the combo box and it
displays the current 'status' field in a 2nd combo box.
I can then select from the status table (ie in use, dead etc) but i
need to take that data and re-insert it back into the consumable
record.

My variables are so:
form name: amend consumables status
consumable table name: dbo_consumables
status field: dbo_consumables.status
combo box (2nd) name: status

I have a tick button below this to take the entry in combo box
'status' for 'onclick' and enter it into the table but i cant get the
code right.

I've tried variations like:

Me!dbo_consumables.STATUS = Forms![amend consumables status]![STATUS]

Me![STATUS] = Forms![amend consumables status]![STATUS]

SELECT tbldbo_consumables.status = Me![STATUS]

and many more but to no available...

most of the time it does not recognise dbo_consumables table...i think
its the syntax i am using.

Any suggestions would be very helpful as this is the last task to
complete my database.

Mark

Nov 12 '05 #2

P: n/a
Hmm... sounds AWFUL familiar... see Allen Browne's website. No need
to re-add it to anything. Just switch the flag.
Nov 12 '05 #3

P: n/a
I have ODBC connectors in palce and yes it is a SQL server database
with an access frontend.
The database is basically complete but i just need this last refernce
however any code i use VB comes back and tells me it cant find the
table.

the page itself looks like this:

combo box drop down list of consumables (from table 'dbo_consumables')

a text box showing current status (a expression grabbing the value
from the selection in the combo box)

that value is also placed in a 2nd combo box(change status). the user
can then select a new value for that consumable_id record.

Below is a command button (a big tick). Within the 'on click '
function i want to update the relevant Consumable_id record with the
new value from combo box 2.

I need a expression of bit of VB code to do this however my syntax is
all screwed up me thinks.

Any idea's?

Mark
Nov 12 '05 #4

P: n/a
i seem to have made a litte progress. with the statement

Me!dbo_consumables.STATUS = Forms![AMEND CONSUMABLES STATUS].[STATUS]

I can update the record but it always update the first record in the
table 'dbo_consumables' and not the one selected from the combo box.
Nov 12 '05 #5

P: n/a
ba******@hotmail.com (MARK) wrote in message news:<be*************************@posting.google.c om>...
i seem to have made a litte progress. with the statement

Me!dbo_consumables.STATUS = Forms![AMEND CONSUMABLES STATUS].[STATUS]

I can update the record but it always update the first record in the
table 'dbo_consumables' and not the one selected from the combo box.


Do all of your consumables have a unique ID or something? Make life
easy on us... post the relevant parts of your table structure. I feel
like I'm playing pin the tail on the donkey. How do you determine
*which* consumable you've given out? Are they unique or generic?
Seeing the relevant part(s) of your database structure would help a
lot... debugging SQL is a lot easier than guessing what the SQL is and
trying to debug it.
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.