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
> Dlookup A Table Record = Form Field, Access 2010    
post Sep 15 2016, 12:34 PM

Posts: 62
Joined: 15-January 11

Good morning,

I am trying to create an update SQL statement that will look to see if a field already exists in the database. I am having trouble with the Dlookup finding the record to see if one already exists. If it does then run the SQL update, if not the second if statement would add a record (not shown).
Here is my thinking:

If Me.cboFolderNo= Dlookup("FolderNo", "tblDocumentData", "") Then
DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE tblDocumentData SET FolderRequestBy = cboFolderRequestBy, FolderRequestDate = txtFolderRequestDate, FolderReceiveDate = txtFolderReceiveDate, FolderLocation = txtFolderLocation, BoxNo = txtBoxNo, NewboxNo = txtNewBoxNo WHERE FolderNo = cboFolderNo"

DoCmd.SetWarnings True
Exit Sub
end if

Where am I going wrong?
Thanks for any help...!
Go to the top of the page
Doug Steele
post Sep 15 2016, 12:50 PM

UtterAccess VIP
Posts: 22,191
Joined: 8-January 07
From: St. Catharines, ON (Canada)

Can you explain what you mean by "see if a field already exists in the database"?

Using DLookup without a WHERE condition means you're going to get a random row back, so I don't think your If statement is particularly meaningful. And even if it did, your SQL statement isn't valid. Values must be outside of the quotes, as in:

  "UPDATE tblDocumentData SET FolderRequestBy = '" & cboFolderRequestBy  & _
    "', FolderRequestDate = " & Format(txtFolderRequestDate, "\#yyyy\-mm\-dd\#") & _
    ",  FolderReceiveDate = " & Format(txtFolderReceiveDate, "\#yyyy\-mm\-dd\#") & _
    ", FolderLocation = '" & txtFolderLocation & _
    "', BoxNo = '" & txtBoxNo & _
    "', NewboxNo = '" & txtNewBoxNo & "'" & _
    "WHERE FolderNo = cboFolderNo

I'm assuming FolderNo is a numeric field, FolderRequestBy, FolderLocation, txtBoxNo and NewboxNo are all text fields and FolderRequestDate and FolderReceiveDate are date/time fields (and you're only storing dates, not dates and times)
Go to the top of the page
post Sep 15 2016, 12:51 PM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


In a DLookup, you name the field you're getting a value from, the table where that field is located, and then the criteria for narrowing down which record's value you want. Your criteria is blank right now.

The way to determine what to put in the criteria is to ask yourself, how would I look in the table manually to find out whether that record already exists or not.

Just to clarify: A "field" is like a storage box. The values you store in your table are things that go IN the box. So, a field would be "txtFolderRequestDate", and a value that you store in that field would be something like #6/24/2016# or #10/31/2015#.

Hope this helps,

Go to the top of the page
post Aug 16 2019, 09:23 AM

Posts: 62
Joined: 15-January 11

Here is the code that works...

'Check for Existing Folder
If Me!cboAddFolderNo = DLookup("[FolderNo]", "[tblMVPDocData]", "[FolderNo]='" & Me!cboAddFolderNo & "'") Then
MsgBox "A Folder with this Number already exists, please enter a new number."
Me.cboAddFolderNo = ""
Exit Sub
End If

Thanks again for all the help!
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th September 2019 - 11:37 AM