Add records to related tables

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

Well, I was going to say "use a subform", but ... !

I don't have Access here to check, so this might or might not work. Create
an updatable query on the two tables. Base a new form on that query. Use
that form to add an order for a new client. Access should create the client
& order records ok. Now add another new record, for a new order for that
(same) client. See if Access is smart enough to >not< re-add the client
record. If so, there's one way to do it.

Another possibility would be to use an unbound form providing a "flat file"
view of the joined data, & do all the necessary updates through VBA code
behind the form.

Why do you not want to use subforms?

HTH,
TC
 
I have two tables: Client(primary key is ClientID) and
orderitems (clientid is a foreign key in the orderitems
table)
this is a one to many relationship.

Using a form,

What is the best way to add to the orderitems table and
have have the orderitems clientid (FK) field populated
with the correct Clientid?

Does access do this automatically? How do I do this
programmically?

ps. I do not want to use subforms.
 
What is the best way to add to the orderitems table and
have have the orderitems clientid (FK) field populated
with the correct Clientid?
Subforms.

Does access do this automatically? How do I do this
programmically?

ps. I do not want to use subforms.
Click to expand...

"I need to cut some lumber, but I don't want to use a saw".

You could do it with a separate Form with VBA code to load the tables,
or unbound forms and VBA code to load BOTH tables - either way will be
much harder to implement and unless you're really clever, much harder
for the user than a subform! Why the aversion?
 
The reason I do not want to use subforms is I want to
create a VB front end for the database. I also want to use
custom COM components to handle updates.



Would you happen to have some sample code that updates the
tables? VBA is fine I'm just trying to determine the most
efficient way to do this through code. Thanks
 
Huh? You said: ">>Using a form<<, what is the best way...". Now you want to
use VBA - not a form. And a COM component has turned up somehow!

So is your question, "How do I add records to tables using VBA from inside a
COM component" ?

You add records to tables in VBA, using the Execute method of the Database
object; for example:

dim db as database
set db = currentdb()
db.execute "INSERT INTO MyTable (Field1, Field2) VALUES (111, 222)",
dbfailonerror
set db = nothing

Or by using the AddNew method on a Recordset object:

dim db as database, rs as recordset
set db = currentdb()
set rs = db.openrecordset ("MyTable")
with rs
.addnew
![Field1] = 111
![Field2] = 222
.update
end with
set rs = nothing
set db = nothing

But whether you can do any of that in a COM component, I have no idea.

HTH,
TC
 
I am familiar with how to add records through code, but I
guess i'm asking about the best way to write the code to
satisfy the foreign key issue.

I will take the code that handles this problem and putit
in a custom class. I have a way that does work but I'm
not sure its the easiest or best way to do it.

Code in Custom Class (COM Component):

Public Function AddDistribution(SC As String, dist As
Integer, ChannelName As String) As Boolean

On Error GoTo AddError
Dim strSQL As String

Dim scid As Integer

Dim rsSCID As ADODB.Recordset

Set rsSCID = GetServiceCenter(SC)

With rsSCID
scid = !scid 'Foreign Key
End With

strSQL = "INSERT INTO tblChannel(dist,scid,channelname)" &
_
"VALUES('" & dist & "','" & scid & "','" &
ChannelName & "')"

CN.Execute strSQL

AddDistribution = True

Exit Function

AddError:

AddDistribution = False

End Function

Public Function GetServiceCenter(Optional SC As String) As
ADODB.Recordset

Dim strSQL As String

On Error GoTo SCError

If SC <> "" Then
strSQL = "Select * from tblservicecenter where SCName='" &
SC & "'"
Else
strSQL = "Select * from tblservicecenter"
End If

Set GetServiceCenter = CN.Execute(strSQL)

Exit Function

Code on the form:

Private Sub AddChannel_Click()

Dim add As Boolean

add = obj.AddDistribution(Me.SC, Me.dist, Me.channelname)

If add = False Then
MsgBox "Could not add Record"
End If

End Sub
-----Original Message-----
Huh? You said: ">>Using a form<<, what is the best way...". Now you want to
use VBA - not a form. And a COM component has turned up somehow!

So is your question, "How do I add records to tables using VBA from inside a
COM component" ?

You add records to tables in VBA, using the Execute method of the Database
object; for example:

dim db as database
set db = currentdb()
db.execute "INSERT INTO MyTable (Field1, Field2) VALUES (111, 222)",
dbfailonerror
set db = nothing

Or by using the AddNew method on a Recordset object:

dim db as database, rs as recordset
set db = currentdb()
set rs = db.openrecordset ("MyTable")
with rs
.addnew
![Field1] = 111
![Field2] = 222
.update
end with
set rs = nothing
set db = nothing

But whether you can do any of that in a COM component, I have no idea.

HTH,
TC


The reason I do not want to use subforms is I want to
create a VB front end for the database. I also want to use
custom COM components to handle updates.



Would you happen to have some sample code that updates the
tables? VBA is fine I'm just trying to determine the most
efficient way to do this through code. Thanks
might
not work. Create on
that query. Use should
create the client new
order for that re-
add the client in
message
Click to expand...



.
Click to expand...
 
Back
Top