Assign a value to a different table

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

Another question for ya's

I have some code that does some evaluation of data. This part I've done.
What I want to do know is change a Yes/No switch on a different table. I
don't know how to code for this. I'm assuming I need :

DoCmd.OpenTable "tblShipment"
DoCmd.GoToRecord acDataTable, "tblShipment", acLast


But how do I set [tblShipment].[ShipmentSwitch] to Yes or No ???

Thanks for the help -- again :-)

Sean
 
-----Original Message-----
Another question for ya's

I have some code that does some evaluation of data. This part I've done.
What I want to do know is change a Yes/No switch on a different table. I
don't know how to code for this. I'm assuming I need :

DoCmd.OpenTable "tblShipment"
DoCmd.GoToRecord acDataTable, "tblShipment", acLast


But how do I set [tblShipment].[ShipmentSwitch] to Yes or No ???

Thanks for the help -- again :-)

Sean


.
to use the code you showed above, i think you'd need to
Click to expand...
declare a TableDef and act on it - which is where i always
get lost.
as another option, you could try this:

Dim Rst As DAO.Recordset

Set Rst = CurrentDb.OpenRecordset("TableName",
dbOpenDynaset)

Rst.MoveLast
Rst.Edit
Rst("FieldName") = 0
' above sets to No, use -1 for Yes
Rst.Update

Rst.Close
Set Rst = Nothing

Last record has no static value in an Access table, so be
sure you force the Last Record you want by using an index
at the table level (i THINK it will carry over to the
recordset) - or use a query or sql statement with an Order
By clause in the openrecordset action, in place
of "TableName".
 
Another question for ya's

I have some code that does some evaluation of data. This part I've done.
What I want to do know is change a Yes/No switch on a different table. I
don't know how to code for this. I'm assuming I need :

DoCmd.OpenTable "tblShipment"
DoCmd.GoToRecord acDataTable, "tblShipment", acLast


But how do I set [tblShipment].[ShipmentSwitch] to Yes or No ???
Click to expand...

ummm...

Insufficient information.

tblShipment could, concievably, contain a couple of million records.

Which record do you want to change?

The acLast record IS NOT WHAT YOU THINK. It's the last record in
physical disk storage order. That order is *totally arbitrary and not
controllable* - it might be the most recently entered record but it
probably won't be.

You will need a totally different technique to do what you want - in
particular you will need to find the record by the value of one or
more fields IN THE RECORD; and open a Recordset based on a Query
selecting that record. OpenTable is the wrong tool.
 
John,

Okay, let's suppose I've run my little algorithm and now I know I want to
set [tblShipment].[ShipmentCreateSwitch] on the 7th record to YES. How do I
change it's YES/NO flag through code?

Sean
 
John, Thanks for the warning about acLast; however, I am aware of the
problems you describe. My code is able to determine exactly which record I
want. My problem is that I can't seem to access it or modify the fields in
it through code behind a form that uses a different table as the datasource.

Let's say my code evaluates it's expression and determines that it wants to
modify the ShipmentCreateSwitch of (for arguments sake) the seventh
record -- or if it's easier -- create a new record. How do I code it to
change the flag from FALSE to TRUE?
 
My problem is that I can't seem to access it or modify the fields in
it through code behind a form that uses a different table as the datasource.

Let's say my code evaluates it's expression and determines that it wants to
modify the ShipmentCreateSwitch of (for arguments sake) the seventh
record -- or if it's easier -- create a new record. How do I code it to
change the flag from FALSE to TRUE?
Click to expand...

Open a Recordset object retrieving (ideally) just the one record you
want to update, and use that Recordset's Update method. This can be
done in either ADO or DAO (a bit differently) - which are you using?

Try looking up the online help for Recordset to see if that gets you
further - if not, please post back indicating which object model
you're using.
 
OK here goes...

First, thanks for your help so far.

Now, I tried using this code (based on what Tina proposed), but I got errors
about "User Type Not Defined"

'Dim ShipRec As Recordset

Set ShipRec = CurrentDb().OpenRecordset("tblShipment", dbOpenDynaset)
ShipRec.AddNew
ShipRec.Edit
ShipRec![ShipmentCreateSwitch] = True
ShipRec.Update
ShipRec.Close
Set ShipRec = Nothing

Then I tried looking up the ADO DAO stuff you mentioned and after many
headaches I figured out (by complete guess work) I need some references to
even get help with this stuff. So I checked off a whole bunch of references
but I don't know what I need and what I don't. In the help file was a
useful little bit of code that I used that told me I'm using DAO. (Here's
that code in case it matters)

Dim rs As Object
Set rs = Forms(0).Recordset
If TypeOf rs Is DAO.Recordset Then
MsgBox "DAO recordset"
ElseIf TypeOf rs Is ADODB.Recordset Then
MsgBox "ADO recordset"
End If

It appears I'm in way over my head again. Thanks

Sean
 
Update here John,

Ok I got it to do what I want. I do have two questions though.

1) Why am I UNable to get help on methods? I know I have all help files
set to run off my drive.

2) What references do I need / how would I know I need a specific
reference?

Thanks for assistance.

Sean

Sean said:
OK here goes...

First, thanks for your help so far.

Now, I tried using this code (based on what Tina proposed), but I got errors
about "User Type Not Defined"

'Dim ShipRec As Recordset

Set ShipRec = CurrentDb().OpenRecordset("tblShipment", dbOpenDynaset)
ShipRec.AddNew
ShipRec.Edit
ShipRec![ShipmentCreateSwitch] = True
ShipRec.Update
ShipRec.Close
Set ShipRec = Nothing

Then I tried looking up the ADO DAO stuff you mentioned and after many
headaches I figured out (by complete guess work) I need some references to
even get help with this stuff. So I checked off a whole bunch of references
but I don't know what I need and what I don't. In the help file was a
useful little bit of code that I used that told me I'm using DAO. (Here's
that code in case it matters)

Dim rs As Object
Set rs = Forms(0).Recordset
If TypeOf rs Is DAO.Recordset Then
MsgBox "DAO recordset"
ElseIf TypeOf rs Is ADODB.Recordset Then
MsgBox "ADO recordset"
End If

It appears I'm in way over my head again. Thanks

Sean



wants
to

Open a Recordset object retrieving (ideally) just the one record you
want to update, and use that Recordset's Update method. This can be
done in either ADO or DAO (a bit differently) - which are you using?

Try looking up the online help for Recordset to see if that gets you
further - if not, please post back indicating which object model
you're using.
Click to expand...
Click to expand...
 
Update here John,

Ok I got it to do what I want. I do have two questions though.

1) Why am I UNable to get help on methods? I know I have all help files
set to run off my drive.
Click to expand...

Because Microsoft totally screwed up the Help File indexing with
Access 2000... and, to the dismay of many of us, seem to have made it
even WORSE in A2002. You might be able to get a bit more info by
opening the VBA editor (this links in a different help file) but it's
still very sporadic.
2) What references do I need / how would I know I need a specific
reference?
Click to expand...

Experience.
 
Ahh... Of course!!! :-)
Thank you.

Sean

John Vinson said:
Because Microsoft totally screwed up the Help File indexing with
Access 2000... and, to the dismay of many of us, seem to have made it
even WORSE in A2002. You might be able to get a bit more info by
opening the VBA editor (this links in a different help file) but it's
still very sporadic.


Experience.
Click to expand...
 
Thank you John, I really appreciate your help. One more step closer to
being satisfied with this DB.

Sean


John Vinson said:
Okay, let's suppose I've run my little algorithm and now I know I want to
set [tblShipment].[ShipmentCreateSwitch] on the 7th record to YES. How do I
change it's YES/NO flag through code?
Click to expand...

Since Access tables don't have record numbers, and since another user
might in principle have inserted sixteen records between the time you
identify the record and the time you insert, that's the wrong
question.

Let's ask it a different way: say you have a set of query criteria
which uniquely identifies the record.

The following code will do the job:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(tblShipment, dbOpenDynaset)
rs.FindFirst "[fieldname] = ... " <<< your criteria for the record
If rs.NoMatch Then
MsgBox "Record not found, unable to edit"
Else
rs.Edit ' open the recordset for updating
rs!ShipmentCreateSwitch = True
rs.Update ' commit the desired change
End If
rs.Close ' clean up
Set rs = Nothing
Click to expand...
 
Back
Top