UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Inserting Record, Access 2016    
 
   
SallyCh
post Feb 21 2020, 05:33 AM
Post#1



Posts: 102
Joined: 24-October 14
From: UK


Hi all

Could someone please let me know where I'm going wrong.

Dim idate as integer
dim strtmp as string

idate = Nz(DLookup("inputid", "tblinput", "userfk = Forms!login!cboname and received = Date()"), 0)

if idate = 0 then
msgbox "test"
strtmp = "INSERT INTO tblinput (userfk, received) VALUES (Forms!login!cboname, Now())"
end if

I've also tried strtmp = "INSERT INTO tblinput (userfk, received) VALUES (Forms!login!cboname, #Now()#)"

the msgbox is firing and I am not getting an error message, but a record is not being inserted.

I usually save a Append Query and then run that, but I want to get to grips with SQL coding.

Any help would, be appreciated.

Thanks
Go to the top of the page
 
cheekybuddha
post Feb 21 2020, 05:45 AM
Post#2


UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill


Hi,

In VBA you must concatenate the referenced values in to your SQL strings,

Also dates must be delimited with hash marks and formatted in an unambiguous format (either american mm/dd/yyyy or ISO yyyy-mm-dd):
CODE
  Dim idate as integer
  dim strtmp as string

  idate = Nz(DLookup("inputid", "tblinput", "userfk = " & Forms!login!cboname & " AND  received = " & Format(Date(), "\#yyyy\-mm\-dd hh:nn:ss\#"), 0)

  if idate = 0 then
    msgbox "test"
    strtmp = "INSERT INTO tblinput (userfk, received) VALUES (" & Forms!login!cboname & " , " & Format(Now(), "\#yyyy\-mm\-dd hh:nn:ss\#") & ");"
  end if


Note You are unlikely to get a value for idate if received contains a time portion.

My guess is you prabably need:
CODE
' ...
  idate = Nz(DLookup("inputid", "tblinput", "userfk = " & Forms!login!cboname & " AND  received >= " & Format(Date(), "\#yyyy\-mm\-dd\#") & " AND  received < " & Format(Date() + 1, "\#yyyy\-mm\-dd\#"), 0)
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
SallyCh
post Feb 21 2020, 07:26 AM
Post#3



Posts: 102
Joined: 24-October 14
From: UK


Thanks for your comments.

My idate doesn't appear to be the problem. If there is no record, it returns 0 and when I manually put the record in, it gives the inputID.

I seem to have a fundamental problem with the INSERT SQL.

There are only 3 fields in the table; InputID, UserFK & Received. I've changed the Default Value of Received to Now(), so I only need to insert the userFK

I stripped it right back to basics and was planning to slowly build it up, one stage at a time, so I could identify the issue, as I assumed it was the Date that was causing the problem, but none of these work.

dim strtmp as string
dim iuser as integer
iuser = forms!login!cboname

strtmp = "INSERT INTO tblinput (userfk) VALUES (iuser)"
or
strtmp = "INSERT INTO tblinput (userfk) VALUES (" & iuser & ")"
or
strtmp = "INSERT INTO tblinput (userfk) VALUES (" & forms!login!cboname & ")"
or
strtmp = "INSERT INTO tblinput (userfk) VALUES ('29')"
or
strtmp = "INSERT INTO tblinput (userfk) VALUES (29)"

I've also tried:
DoCmd.RunSQL = "INSERT INTO tblinput (userfk) VALUES ('29')"

but get the error message: Compile error: Argument not optional

I've double checked the name of the table and the field names, in case I had a typo but all is good & I've noticed that the case is not being changed from tblinput and userfk to TblInput and UserFK

I've now created an Append query and it worked:

INSERT INTO TblInput ( UserFK )
SELECT [forms]![login]![cboname] AS Expr1;

so I cleared all the coding from the button and used this, which works:
DoCmd.OpenQuery "QryTest", acViewNormal, acEdit

I could just run the saved query, but I really wanted to make it work in VBA/SQL



Go to the top of the page
 
cheekybuddha
post Feb 21 2020, 07:34 AM
Post#4


UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill


>> I've also tried:
DoCmd.RunSQL = "INSERT INTO tblinput (userfk) VALUES ('29')" <<

Your error here is the '=', but I would advise never to use DoCmd.RunSQL.

Much better to use CurrentDb.Execute.

I assumed you had that (or DoCmd.RunSQL ...) in your code, just hadn't posted it. Now I'm wondering whether I made an assumption too far.

How were you executing your strtmp?

Your code should look like:
CODE
  Dim strtmp As String
  Dim iuser As Integer

  iuser = Forms.login.cboname
  strtmp = "INSERT INTO tblinput (userfk) VALUES (" & iuser & ")"
  Debug.Print strtmp
  CurrentDb.Execute strtmp, dbFailOnError


Also, will you confirm that Forms.login.cboname returns a numeric value, not the actual username.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
SallyCh
post Feb 21 2020, 07:50 AM
Post#5



Posts: 102
Joined: 24-October 14
From: UK


Oh I feel a real dummy, I wasn't executing the strtmp. I knew enough to realise I was missing something but not enough to know what.

Your suggested coding worked and, yes, iuser is a number. thank you so much for your patience.
Go to the top of the page
 
cheekybuddha
post Feb 21 2020, 08:05 AM
Post#6


UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill


You're very welcome!

>> Oh I feel a real dummy <<
Really don't!! We have all been there, and in my case, often several times a day!

Continued success with your project!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
SallyCh
post Mar 2 2020, 07:48 AM
Post#7



Posts: 102
Joined: 24-October 14
From: UK


I thought I had sorted this out but I'm still having issues.

I wrongly thought my Dlookup was working but it wasn't. I have re-read your comments and am trying to sort it, but even though I'm not getting any errors, it keeps adding a new record.

I've broken it down to iUser, dDate and dDate1 to simplify it for myself and Access then identifies dDate and dDate1 as dates, so I don't have to add #. when I hover over each part, it displays the correct data, but it keeps giving iInput = 0
Note: I'm using UK date format

Private Sub CmdNew_Click()
Dim strtmp As String
Dim iUser As Integer
Dim iInput As Integer
Dim dDate As Date
Dim dDate1 As Date

iUser = Forms.login.cboname
dDate = Format(Date, "dd/mm/yyyy")
dDate1 = Format(Date + 1, "dd/mm/yyyy")

iInput = Nz(DLookup("inputid", "tblinput", "userfk = (" & iUser & ") And Received >= (" & dDate & ") And Received < (" & dDate1 & ")"), 0)

strtmp = "INSERT INTO tblinput (userfk) VALUES (" & iUser & ")"

If iInput = 0 Then

Debug.Print strtmp
CurrentDb.Execute strtmp, dbFailOnError
End If
DoCmd.OpenForm "frmmlog", acNormal, , , acFormAdd, acWindowNormal


End Sub
This post has been edited by SallyCh: Mar 2 2020, 08:06 AM
Go to the top of the page
 
strive4peace
post Mar 2 2020, 07:44 PM
Post#8


strive4peace
Posts: 20,527
Joined: 10-January 04



hi Sally,

instead of formatting the date and concatenating the formatted string, you can use Date() directly in the criteria such as:

CODE
"( (userfk = " & iUser & ") And (Received >= Date()) And (Received < (Date() +1) )"


If you take Date(), or Date()+1, out of the string, they need # delimiters around them. Access can process the Date() function though -- so not necessary to do that wink.gif



This post has been edited by strive4peace: Mar 2 2020, 07:45 PM

--------------------
have an awesome day,
crystal

Microsoft MVP
Remote Training and Programming -- let's connect and build your application together! MsAccessGurus.com
.
Go to the top of the page
 
Gustav
post Mar 3 2020, 05:02 AM
Post#9


UtterAccess VIP
Posts: 2,206
Joined: 21-February 07
From: Copenhagen


You can do it with a single command:

CODE
    Dim iUser   As Integer
    Dim strTmp  As String
    
    iUser = Val(Forms!login!cboname.Value)
    
    strTmp = _
        "Insert Into tblInput (UserFK, Received) " & _
        "Select Top 1 " & iUser & ", Date() " & _
        "From tblInput " & _
        "Where " & _
        "    (Select UserFK " & _
        "    From tblInput " & _
        "    Where UserFK = " & iUser & " And Received = Date()) Is Null"
    
    CurrentDb.Execute strTmp, dbFailOnError

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
SallyCh
post Mar 4 2020, 04:27 AM
Post#10



Posts: 102
Joined: 24-October 14
From: UK


Thanks for the suggestions.

I tried Strive4peace's suggestion:
iInput = Nz(DLookup("inputid", "tblinput", "userfk = (" & iUser & ") And Received >= date() And Received < date()+1"), 0)

It still adds a new record each time I run it, so it still sees iInput = 0.

I also tried Gustav's suggestion, but I got a Run-time error '3346': Number of query values and destination fields are not the same.

I've revisited Cheekybudda's suggestion and it works with their date format. but when I try and change that to UK date (dd/mm/yyyy), I'm back to square one. I've tried:

Format(Date(), "\#dd\-mm\-yyyy\#")
Format(Date(), "#dd/mm/yyyy#")
Format(Date(), "\#dd\/mm\/yyyy\#")
Format(Date(), "#dd-mm-yyyy#")

This is Cheekybudda's option, which works (I did put in Date() but Access removed the ())
idate = Nz(DLookup("inputid", "tblinput", "userfk = " & Forms!login!cboname & " AND received >= " & Format(Date, "\#yyyy\-mm\-dd\#") & " AND received < " & Format(Date + 1, "\#yyyy\-mm\-dd\#"), 0)

Thanks
Go to the top of the page
 
Gustav
post Mar 4 2020, 05:18 AM
Post#11


UtterAccess VIP
Posts: 2,206
Joined: 21-February 07
From: Copenhagen


QUOTE
Number of query values and destination fields are not the same.


Then you haven't copied the code correctly. So, remove the typos.
Code is tested and runs as expected.

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
cheekybuddha
post Mar 4 2020, 05:36 AM
Post#12


UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill


>> but when I try and change that to UK date (dd/mm/yyyy) <<

Don't try and change it - use the format I suggested: "\#yyyy\-mm\-dd hh:nn:ss\#"

It does not affect how you view dates in your database. It just makes sure that the database knows it's storing the correct date, irrespective of local regional settings

--------------------


Regards,

David Marten
Go to the top of the page
 
SallyCh
post Mar 4 2020, 06:04 AM
Post#13



Posts: 102
Joined: 24-October 14
From: UK


Thanks to everyone for all your help.

Go to the top of the page
 
cheekybuddha
post Mar 4 2020, 09:11 AM
Post#14


UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill


@Gustav

>> Code is tested and runs as expected. <<
Are you sure? The 'Top' looks out of place, or missing a '1'.

@SallyCh
I hope you got the solution you needed. yw.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
Gustav
post Mar 5 2020, 05:02 AM
Post#15


UtterAccess VIP
Posts: 2,206
Joined: 21-February 07
From: Copenhagen


QUOTE
The 'Top' looks out of place, or missing a '1'.

Yes, it vanished somehow. Thanks!

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
cheekybuddha
post Mar 5 2020, 05:58 AM
Post#16


UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
strive4peace
post Mar 5 2020, 09:57 AM
Post#17


strive4peace
Posts: 20,527
Joined: 10-January 04



you're welcome, Sally, glad you go it to work!

> I did put in Date() but Access removed the ()
yes, it will -- but putting in parentheses ensures that Access will know it is the function. More important when typing expressions for queries. Just a good habit.

--------------------
have an awesome day,
crystal

Microsoft MVP
Remote Training and Programming -- let's connect and build your application together! MsAccessGurus.com
.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    1st April 2020 - 11:13 PM