Help with Insert Into

[tompk]

I've posted a couple times with no success but am probably not asking the
right questions. I have done a ton of searching online and think that what I
need to use is Insert Into however, I keep getting an error message and
programming is not my strong suit.

Essentially what I want is for the user to click search on a continuous
subform, search for the record they want and add it. The Search form is
built and works great by producing a list of records that the user can double
click to select the one they want however, I cannot figure out how to add the
record to my subform. Here's my setup:

Tbl_MAIN with Record_ID (PK)
Subtbl_Obligations_MAIN with Oblig_ID (PK)
Tbl_JUNCTION with Record_ID, Oblig_ID (for my many-to-many)

The main form in this instance is Frm_Obligations_MULTIPLES and the subform
is Frm_MAIN_MULTIPLES (probably not the best naming). The users need to be
able to add multiple Tbl_MAIN records to the Obligation. Both of the forms
are actually built off of a query of the respective tables, not the tables
themselves.

I found some code online which I have tried to modify for my own purposes:

Private Sub List_Results_DblClick(Cancel As Integer)
'Insert Record from main table into Frm_MAIN_MULTIPLES
Dim strSQL As String
strSQL = "INSERT INTO TBL_JUNCTION " & _
"(Oblig_ID, Record_ID) " & _
"VALUES(" & Forms!Frm_MAIN_MULTIPLES!Oblig_ID & _
", " Me.Record_ID & ")"
End Sub

I am not sure if I am even on the right track so I would really appreciate
if someone could advise. Additionally, I keep getting an "Expected end of
statement message" around the last two lines. I've tried playing around with
the "'s but am not getting anywhere.

From what I understand, my next step would be to put
Forms!Frm_Obligations_MULTIPLES!Frm_MAIN_MULTIPLES.Requery on After Insert.
True?

Thank you so much for any assistance you can provide

 

[code_monkey_number_9]

Try this for the Insert SQL:

Dim ObligID as Long
Dim RecID as Long

ObligID = Forms!Frm_Main_Multiples!Oblig_ID
RecID = Me.Record_ID

str = "Insert Into TBL_JUNCTION (Oblig_ID, Record_ID) Values (" _
& ObligID & ", " & RecID & ");"

In your post, you are missing a space between the VALUES and the open paren
following it, and you are also missing the ampersand between ", " and
Me.Record_ID. That last omission could be causing the 'expected end of
statement' error. The first would cause the SQL to fail. Separating out the
variables as I did is optional, but I find it easier to read and easier to
debug.

From there you need to execute the sql statement, for example:

Dim cmd as New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = str
cmd.execute

Requery the form once the underlying recordset has been updated.

 

[tompk]

Thanks a lot Code Monkey! I feel like I am almost there! I have changed my
code to yours but now, I am getting an error on str "Compile Error: Argument
not optional"

Is there a way for me to make the Argument Optional?

Thanks again!

 

[code_monkey_number_9]

I used str where you used strSQL. Did you dim str, e.g.:

Dim str As String?

Or, you could change str back to strSQL.

 

[tompk]

well that was obvious (sorry) and now I feel silly asking another question.
When I run it I was first getting an error "can't find
form"Frm_MAIN_MULTIPLES...." on:

ObligID = Forms!Frm_MAIN_MULTIPLES!Oblig_ID

I tried changing it to

ObligID = Forms!Frm_Obligations_Multiples!Frm_MAIN_MULTIPLES!Oblig_ID

and now I get an error message "Invalid use of Null"

All I can guess is that it doesn't like that there is no Oblig_ID in the
subform because I actually haven't entered anything yet? Do I have to make
the user enter something first in order to get this to work?

I have the subform set up with the Record_ID and Oblig_ID from Tbl_JUNCTION
which I thought would be correct because that is what I am trying to fill in.

Arrrrgggghhh, I'm pulling my hair out on this one.

 

[John Spencer]

I would avoid using str as a variable since it is a VBA function to
convert a variable/value into a string. It should work if you Dim str
as String, but ...

You have dimmed ObligID as Long. So when you attempt to assign a NULL to
it, you will get an error. You can Dim ObligId as Variant or test
Forms!Frm_Obligations_Multiples!Frm_MAIN_MULTIPLES!Oblig_ID to see if it
is null

IF IsNull(Forms!Frm_Obligations_Multiples!Frm_MAIN_MULTIPLES!Oblig_ID) THEN
'Do something to handle the problem
ELSE
ObligID = Forms!Frm_Obligations_Multiples!Frm_MAIN_MULTIPLES!Oblig_ID
End IF

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[code_monkey_number_9]

alright, first off, I'm having some trouble understanding exactly what you
are trying to do, since I don't have you or your db in front of me.

We originally started talking about a procedure in what appeared to be a
double click event of a list box. If the user is selecting a record from the
list box, and the query behind the list box has the appropriate record id's,
perhaps you could get your values from it rather than the form/ subform.

Can you describe your forms in a bit more detail, e.g.,

Main Form is Frm_Obligations_Multiples, whose recordsource is ...
Sub Form is Frm_Main_Multiples, whose recordsource is ...

The user clicks on a list box in the subform/ main form. the listbox has a
recordsource of ...

When the user dbl clicks on the listbox, {this} should happen in the
subform, and {this} should happen in the main form, and (or) {this} should
happen to the underlying recordsets of ...

deep breath, relax, write.

 

[tompk]

Ok, here goes (and thank you for all your help!):

Main Form is Frm_Obligations_Multiples, whose recordsource is
Qry_Obligations_MAIN which is based off of Subtbl_Obligations_MAIN
Sub Form is Frm_Main_Multiples, whose recordsource is
Qry_Tbl_Main_For_Obligations which is based off of Tbl_MAIN with the Oblig_ID
and Record_ID from Tbl_JUNCTION

As background, I work for an Oil & Gas Co. in Regulatory and my monstous
spare time job is getting this db up and running with all the relevant job
functions. Each job function requires us to track specific information that
is always tied to one location (well, facility, pipeline) and I have lots of
one-to-many relationships that work great. Each location (as we have a
problem) is added to the database by a UWI which then calls our corporate
hierarchy and fills in all the area information (ie. District, Field)

In this instance Obligations we are talking about a different beast where we
may self-declare (for example) to the govt. that we have to make a correction
at say 20 different locations (such as all our signs are wrong).

There are currently approx. 5,000 records (with many more to come) and the
user may only know the UWI or the license or the District etc. so I have
Search Forms on all our tracking forms which work great in the one-to-many
scenario and open up the correct form.

Now I'm trying to do this in reverse and attach many records from the
Tbl_MAIN to the Obligation Record. What I have in my continuous subform is a
Search Button which opens up a search form (Frm_Search_DB_For_Mult). On the
search form they either enter into some text boxes or select from drop downs
where appropriate and click Search, a list (List_Results) appears and they
can double click the record they want to add (in my one-to-many
relationships, it actually opens up the form for that location).

I would like that record selected to fill into the Frm_MAIN_MULTIPLES and
then the user can continue to add as many records as they need to. Of course
if their search turns up blank, they also need to be able to add a new record
(UWI) but I have always just made them close the Search Form manually and
then enter a new record.

I hope that makes sense. I really thought this was going to be easy but you
can tell that coding is not my strong point!

Based on John's post, I changed Dim str to strSQL and Dim Oblig_ID to
Variant which then knocked my error to Record_ID so I tried changing that too
and now I get an error on the cmd.execute. Here's what I now have:
Private Sub List_Results_DblClick(Cancel As Integer)
'Insert Record from main table into Frm_MAIN_MULTIPLES
Dim Oblig_ID As Variant
Dim Record_ID As Variant
Dim StrSQL As String

Oblig_ID = Forms!Frm_Obligations_Multiples!Frm_MAIN_MULTIPLES!Oblig_ID
Record_ID = Me.Record_ID

StrSQL = "Insert Into TBL_JUNCTION (Oblig_ID, Record_ID) Values (" _
& Oblig_ID & ", " & Record_ID & ");"

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = StrSQL
cmd.Execute

End Sub

 

[code_monkey_number_9]

If changing the datatype to variant caused the error to go away for oblig_id,
that likely means you were trapping a null. For what you are doing, you
don't want that - you need to have a value of whatever type Oblig_ID is in
the underlying table (Subtbl_Obligations_Main). Putting null or nonsense
values in your join table won't get you anywhere - which is why John wanted
you to test for nulls before proceeding with the rest of the sub.

Since ID fields are generally numeric, I had assumed Long would be
appropriate. (They don't have to be, but it is convenient.) At any rate,
what is most important here is that you have an appropriate value to place in
your SQL statement. Passing a null value screws up the SQL statement, which
is probably the cause of the error at cmd.execute.

What is the recordsource for your list_results list box? Does it list
records from tbl_Main? If so, we can get Record_ID from it:
Record_ID = Me.list_results.column(0) 'assuming Record_ID is the first
column in your list box.

As for Oblig_ID, try getting that from the main form instead of the sub form:
Oblig_ID = Forms!Frm_Obligations_Multiples!Oblig_ID

(I am assuming this is the correct Oblig_ID, since the main form is based on
subtbl_obligations_main, and the subform likely forms a parent-child relation
with the main based on this field)

Try (and change your Dim statements to an appropriate data type):

Private Sub List_Results_DblClick(Cancel As Integer)
'Insert Record from main table into Frm_MAIN_MULTIPLES
Dim Oblig_ID As Long
Dim Record_ID As Long
Dim StrSQL As String

Oblig_ID = Nz(Forms!Frm_Obligations_Multiples!Oblig_ID,0)
Record_ID = Nz(Me.list_results.column(0),0)

If Oblig_ID = 0 Or Record_ID = 0 Then
'stop and debug!
Exit Sub
End If

StrSQL = "Insert Into TBL_JUNCTION (Oblig_ID, Record_ID) Values (" _
& Oblig_ID & ", " & Record_ID & ");"

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = StrSQL
cmd.Execute

End Sub

 

[tompk]

I am sending you the biggest smile and thank you right now! You were right
on all counts (the id's were long integer autonum's, the record id was the
first column of the list_results...).

With this latest change, it is now adding the records but does not close the
search form and you don't see the record until you leave the form and come
back. I put my requery on the After Insert of Frm_MAIN_MULTIPLES, am I in
the wrong place?

And thank you again - I am feeling very relieved!

 

[code_monkey_number_9]

To close the search form after the user double clicks a record, add:

DoCmd.Close acForm, Me.Name, acSaveYes

at the end of the list_results_dblclick sub. If you are trying to requery
the subform from which you launched the search form, you can open the search
form in "dialog" mode from the subform. When you do this, you pause code
execution from the subform's module until the dialog closes, at which point
execution resumes. So from the subform's search button click event, you
would open the search form like:

DoCmd.OpenForm "frmSearchFormName", , , , , acDialog

Which you could then follow with:

Me.Requery

See if that works for you.