CmdBtn SQL appends 0 records...why??

[Angi]

Long and short, if the ship to recordset is empty, the user needs to be
able to click a button and fill the ship to info with the same company
info. The only way I could think to do this is with SQL. It's ugly
coding, so if anyone knows a better idea, I'm all ears! The problem is
it says I'm going to append 0 records to the ShipToMain table when it
should be 1. I've looked at the code, it makes perfect logical sense
to me, so I need a new pair of eyes.

Note: The Select is the only thing I see that could be a problem.
It's not all the table fields, but when I add ShipToMain.*, I get the
error that I have too many CoID destinations. If change the SELECT to
ShipToMain.* and nothing else I still get the 0 record error.

DoCmd.RunSQL "INSERT INTO ShipToMain " _
& "SELECT ShipToMain.CoID, ShipToMain.ShipToName,
ShipToMain.SAddress1, ShipToMain.SAddress2, ShipToMain.SCity,
ShipToMain.SState, ShipToMain.SZip, ShipToMain.SPhone, ShipToMain.SFax
" _
& "FROM ShipToMain WHERE
(((ShipToMain.CoID)=[Forms]![CompanyMain]![CoID]) AND
((ShipToMain.ShipToName)=[Forms]![CompanyMain]![CompanyName]) AND
((ShipToMain.SAddress1)=[Forms]![CompanyMain]![Address1]) AND
((ShipToMain.SAddress2)=[Forms]![CompanyMain]![Address2]) AND
((ShipToMain.SCity)=[Forms]![CompanyMain]![City]) AND
((ShipToMain.SState)=[Forms]![CompanyMain]![State]) AND
((ShipToMain.SZip)=[Forms]![CompanyMain]![Zip]) AND
((ShipToMain.SPhone)=[Forms]![CompanyMain]![Phone]) AND
((ShipToMain.SFax)=[Forms]![CompanyMain]![Fax]));"

 

[Rob Oldfield]

Instead of attempting to run the code directly.... build a text string first
(I generally call it sql) and alter your code to docmd.runsql sql. Drop a
breakpoint in after the string has been created and use the immediate window
to copy the sql string. Paste that into a query sql window and see what's
wrong with it.

 

[Angi]

Rob,
Thanks for the reply. I created the sql string in the query window,
but I did what you said anyway. Again, I'm not seeing the problem. To
me, it's pretty straightforward...create a new shipto record where all
the fields are equal to the current forms fields. I'm not getting any
errors now. Just appending 0 records still. I just realized I may
have posted this to the wrong group. Should I create a new post in
queries? Thanks for help!

Ang

 

[Rob Oldfield]

Hmm. I can't say exactly why it's not working... but a couple of points....

Is the control name on the form actually 'Company Name'? If it's
'ShipToName' then that might be why it's not working.

If you actually create the sql including the values taken from the form,
then it would be easier to debug... e.g. instead of
((ShipToMain.CoID)=[Forms]![CompanyMain]![CoID]) so that the query you
create contains form references - try "(((ShipToMain.CoID)="+cstr(me.coid)
instead. That would make it easier to deal with.

As it stands at the moment, then I think you're also going to have problems
with null values. (Though hopefully one of my later suggestions will cope
with that.)

Is CoID a primary key field? Two things come out of this...

Even if you get the query string correct, the query itself isn't going to
work as it's going to be attempting to write a duplicate key field into the
table.

You don't need all the other criteria. If it's a PK then a criteria of
CoID=whatever will be enough.

In fact, if CoID *isn't* a PK, then it (or something else) should be. Then
you can get rid of lots of the complication in your criteria.

How does that look?

 

[Angi]

Rob,
Looks great! I totally agree! Ok...I'm going to answer questions
first.
The control name of the btn is cmdUpdateShip. The form name is
CompanyMain. CoID is not a PK for the ShipToMain table, ShipID is.
CoID is the PK for the compmain table (which isn't used here). I use
the CoID for relationship purposes.

Now, you said try "(((ShipToMain.CoID)="+cstr(me.coid)
Is this what I was supposed to do? (This is now the whole sql string)
sql = "INSERT INTO ShipToMain " _
& "SELECT ShipToMain.* FROM ShipToMain " _
& "WHERE '(ShipToMain.CoID)'=+cstr(me.coid); "

If yes, it now prompts me to enter the coid. Was that purpose? When I
enter the coid, it still appends 0 records. I can't remember the last
time I had a problem coding sql so this has just got me baffled. As
far as null values, all the fields are able to accept null values
except CoID, which is never null. Also, if that were a problem, I
should get an error that says as much. Right?? Is there a better way
to do this than the way I am doing it?? Is this driving you crazy as
much as me??? Thanks again!!

Ang

 

[Rob Oldfield]

Almost there. You're getting the prompt because the query that Access is
trying to run includes a reference to me.coid which isn't understandable by
the query. If you modify your code to...

sql = "INSERT INTO ShipToMain " _
& "SELECT ShipToMain.* FROM ShipToMain " _
& "WHERE '(ShipToMain.CoID)'=+cstr([forms]![CompanyMain]!coid); "

....then the query will be executable (but won't, I think, do exactly what
you're after) as the query will know how to interpret
forms![companymain]![coid]. The reason that your current one prompts is
that me.CoID is only meaningful within the form code. So... next step...

I prefer to actually build the complete query string without any references
to form controls active within the query. That would look something like...

sql = "INSERT INTO ShipToMain " _
& "SELECT ShipToMain.* FROM ShipToMain " _
& "WHERE (ShipToMain.CoID)="+cstr(me.coid)

....so you end up with the string saying INSERT INTO ShipToMain SELECT
ShipToMain.* FROM ShipToMain WHERE (ShipToMain.CoID)=47 i.e. (and to repeat
myself)... the query string but with the actual values that it's going to
use hard coded into it. The big benefit of doing it this way is that you
can then take that string and paste it back into the SQL view of a query
window and see *exactly* what it's going to do.

And then (last bit), if I have what you're after correct then you want to
duplicate the current record. The trick here is just to have something that
identifies uniquely 'the current record'. The easiest way to do that is
going to be to add a PK field onto ShipToMain and also add a control onto
the form (although it can be invisible). Then I think you'll get what you
want by just using your original query string (I haven't checked this)...

INSERT INTO ShipToMain SELECT ShipToMain.CoID, ShipToMain.ShipToName,
ShipToMain.SAddress1, ShipToMain.SAddress2, ShipToMain.SCity,
ShipToMain.SState, ShipToMain.SZip, ShipToMain.SPhone, ShipToMain.SFax FROM
ShipToMain

but with

WHERE (ShipToMain.NewPKField)="+cstr(me.PKFieldControl)

on the end. Note that you won't be able to use ShipToMain.* as that will be
trying to write a duplicate PK value.

 

[Angi]

Rob,

OK...I hate to admit this, but now I'm feeling a little lost. For
starters, I changed my code to include the [Forms]blahblahblah...still
nothing. I put a stop at the docmd and ran the sql line in the sql
window and now I get the following error when trying to run it:

This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables.

Here's what's in the sql window:
INSERT INTO ShipToMain
SELECT ShipToMain.*
FROM ShipToMain
WHERE (((ShipToMain.CoID)=+CStr([Forms]![CompanyMain]![CoID])));

If I stop the debugger, it runs but of course, blank record (0 record).


Where I'm lost is the PK value. The me.CoID uniquely identifies the
record. ShipToMain has a PK, ShipID, which is unique. But won't be
created for the currentrecord until this query runs. I should add that
field to my main form?? Technically it is on my main form, but in the
ShipToSubform, which isn't a problem yet. If I have to add anything
relating to my shipto table onto the main form, I'm going to have to
recode everything that refers to the coid field to compmain.coid. That
would be a real pain, but will do it if necessary. Sorry! I'm trying
to keep up!

 

[Rob Oldfield]

Hmm. I have to admit that I'm a little lost as well. Could you just run
through exactly what it is that you're trying to do? Insert a child record
for parent records where no child record exists? Or something else?

 

[Angi]

Rob,
Exactly! Here's my "vision"...

The cmdBtn (on mainform) will only be visible if the ShipToSubform
recordset is empty.
With Me![ShipToSubform].Form
me.cmdUpdateShip.Visible = (.RecordsetClone.RecordCount = 0)
End With

That works like a charm! If the button is visible, I want to create
the ship to from the company fields. In other words, Same As Above
type thing. Just copy the fields that are relevant, which are the ones
I selected. I know how to populate the subform, it's creating the
record that causing me a problem. It's not for display purposes only,
I need it to become a record. This has to be possible, I'm just doing
something wrong. Crazy yet???

 

[Angi]

Rob,
OK...been working on this and this is what I've come up with:

I'm an moron with a capital M! I'm pulling from the wrong table! The
table I need to pull from is CompMain and insert the info into
ShipToMain.

Here's the ugly looking, but beautiful working code:

Private Sub cmdSameShip_Click()
Dim sql As String
sql = "INSERT INTO ShipToMain ( CoID, ShipToName, SAddress1,
SAddress2, SCity, SState, SZip, SPhone, SFax ) " & _
"SELECT CompMain.CoID, CompMain.CompanyName, CompMain.Address1,
CompMain.Address2, " & _
"CompMain.City , CompMain.State, CompMain.Zip, CompMain.Phone,
CompMain.Fax " & _
"FROM CompMain WHERE
(((CompMain.CoID)=forms!companymain.coid));"

DoCmd.RunSQL sql

DoCmd.RunSQL "UPDATE ShipToMain SET ShipToMain.[Default] = Yes " &
_
"WHERE (([ShipToMain]![CoID]=forms!companymain.coid));"
Me.Requery

End Sub

I had to use the Forms!.. because I kept getting errors on everything
else, including your suggestions. Nothing will be null because
everything is set to text and every field is allowed to be empty.
Sooooo....NO ERRORS! It's gorgeous! I'm so, so sorry for wasting your
time, but you were a really big help! Told you I needed another pair
of eyes!

Thanks so much! I can go to bed now!
Gratefully,
Angi

 

[Rob Oldfield]

Excellent. Glad to hear it. Those problems where you end up having to slap
yourself around the head because you misspelt something are always
frustrating.

One thing worth checking thought... the control showing CoID... you're sure
that the control name is actually CoID? It is better... as I see Ken Snell
suggested in another of your threads... to concatenate the actual value of
the control instead of the form reference.

I'd suggest setting up a little sample just to see how it works. Get back
to me if you want to try that.

 

[Angi]

Rob,
Yes, the control name and control source are both CoID. I actually
tried the ways you and Ken gave me instead of referring to the form,
but I kept getting data type mismatch errors. I know the reason is
because in the ShipToMain table CoID is set to number and I'm trying to
give it a string, but I also tried Int() and that didn't work either.
The form reference works, but I do see the point. If you can tell me
how to fix this, then I'd be glad to hear it.

Ang

 

[Rob Oldfield]

Hmm. The conversion to string is supposed to be done by the CStr
function...

dim i as integer, s as string
i=1
s="a"

i+s won't work while cstr(i)+s will return 1a

Let's just try something (hopefully) simple. Set up a table called
ShipToTest containing just two fields: CoID (autonumber and pk field) and
ShipToName (text). Add a few records on to it. Then create a form based on
that table with both fields on.

Add a button and add this to the click event...

sql = "INSERT INTO ShipToTest ( ShipToName ) " + _
"SELECT ShipToTest.ShipToName " + _
"FROM ShipToTest " + _
"WHERE (((ShipToTest.CoID)=" + CStr(Me.CoID) + "));"
DoCmd.RunSQL sql

Does that work?

 

[Angi]

Ok, it worked. I just noticed something. You didn't declare sql as a
variable first and it worked. I just took out the Dim sql as string
and now it works. I thought all variables had to be declared. Is it
because string is text and SQL is, well...SQL??

 

[Rob Oldfield]

Now that is very strange. If you don't actually dim sql then Access will
just treat it as a variant (which you can get away with but is, generally, a
bad idea). I've just tried it both with and without the dim line in here
and, for me, it works both ways. What happens if you run it including the
dim?

 

[Angi]

Now it works fine...go figure. I did notice some changes in your code
this time from before so maybe that was it. Here's my latest (and
working!) code:

Private Sub cmdSameShip_Click()
DoCmd.SetWarnings False
Dim sql As String

sql = "INSERT INTO ShipToMain ( CoID, ShipToName, SAddress1,
SAddress2, SCity, SState, SZip, SPhone, SFax ) " & _
"SELECT CompMain.CoID, CompMain.CompanyName, CompMain.Address1,
CompMain.Address2, " & _
"CompMain.City , CompMain.State, CompMain.Zip, CompMain.Phone,
CompMain.Fax " & _
"FROM CompMain WHERE (((CompMain.CoID)=" + CStr(Me.CoID) +
"));"

DoCmd.RunSQL sql

DoCmd.RunSQL "UPDATE ShipToMain SET ShipToMain.[Default] = Yes " &
_
"WHERE (([ShipToMain]![CoID]=" + CStr(Me.CoID) + "));"

Me.Requery
DoCmd.SetWarnings True

End Sub

I know didn't make the the 2nd docmd a string, but I figured it was
small so didn't worried about it. Sorry about my ignorance, but what
was the difference between

" & Cstr(me.coid) & " , +cstr(me.coid) ,and " + Cstr(me.coid) +
"

The last one works, the others don't. Just syntax or do they actually
mean different things?

Last question...what are you gonna do with yourself when you don't see
this thread with any new activity?? ;-) Thanks for all your help!!!!

Gratefully,
Angi

 

[Rob Oldfield]

Even stranger... but if it works... then what the hell?

As to the differences in your examples... you're text seems to have run
together a bit when I look at it so I can't tell exactly what your two
different bits of text are... but...

There is a slight difference between the & and + operators...

String + Null gives a null result, String & Null results in String. Which
one you use pretty much depends on the situation. I tend to always use +,
and only start worrying about it when things go wrong.

CStr is just the name of a function. If you just type cstr then Access will
convert it to CStr so it's immaterial which you use.

 

[Michel Walsh]

Hi,

If you use DoCmd rather than CurrentDb, it is preferable to use
FORMS!FormName!ControlName inside the string of the SQL statement since with
that syntax, you can forget about delimiter, ", ' and #, and possible
default locale oddities (like a coma for decimal dot, or yyyy.mm.dd as date
format ), and possible NULLs, and so and so. Unfortunately, CurrentDb does
not recognize the FORMS!FormName!ControlName syntax, and if you use it, you
have to "concatenate" the value, with the right delimiter, watching if the
delimiter is not already part of the data you append, caring about nulls,
.... Criteria of domains function (DLookup, DCount, ... ) recognize
FORMS!FormName!ControlName, so, in those cases too, it may be wise to keep
them "inside" the string, rather than using strings concatenation.




Hoping it may help,
Vanderghast, Access MVP


(...)