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
> Access VBA Criterias, Access 2016    
 
   
Jdk412
post Jan 11 2019, 09:55 AM
Post#1



Posts: 21
Joined: 16-July 18



CODE
id_ = Me.InventoryID 'Number
    SO = [Forms]![New sales order]![Text275] 'Number
    Due = [Forms]![New sales order]![Text115] 'Date
    PM = Me.PartNumber 'String

    rs.FindFirst "[InventoryID]=" & id_ And "[SalesOrder]=" & SO And "[DueDate]=" & "#" & Due & "#" And "[PartNumber]=" & "'" & PN & "'"
    rs.Delete


I'm getting the error type Mismatch I'm assuming it the way I'm typing the criterias

Any help would be appreciated on the correct way to type it out

Go to the top of the page
 
R_Durrer
post Jan 11 2019, 10:18 AM
Post#2



Posts: 95
Joined: 19-April 16
From: Guelph, ON, Canada


Based on the field for Date being [text115] do you need the Hashtags as it is not an actual date, or it could be that you need to convert the date text into a date value and then use the hash tags?
Go to the top of the page
 
mike60smart
post Jan 11 2019, 10:26 AM
Post#3


UtterAccess VIP
Posts: 12,986
Joined: 6-June 05
From: Dunbar,Scotland


Hi

Try something along these lines:-

Dim lngInventoryID as Long
Dim strText275 as String
Dim dteDue as Date
Dim strPM as String

lngInventoryID = Me!InventoryID
strText275 = Me!text275
dteDue = Me!text115
strPM = Me!Partnumber


rs.FindFirst "[InventoryID]=" & id & " And [SalesOrder] = '" & SO & "' And [DueDate]= #" & Me!text115 & "# And [PartNumber]= '" & strPM "'"
rs.Delete

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Minty
post Jan 11 2019, 10:30 AM
Post#4



Posts: 174
Joined: 5-July 16



You have PN in the criteria but PM as your variable. ALso your concatenation isn't quite right.

CODE
rs.FindFirst "[InventoryID]=" & id & " And "[SalesOrder]=" & SO & " And "[DueDate]=#" & Due & "# And "[PartNumber]='" & PN & "'"

This post has been edited by Minty: Jan 11 2019, 10:31 AM
Go to the top of the page
 
Jdk412
post Jan 11 2019, 10:32 AM
Post#5



Posts: 21
Joined: 16-July 18



Thanks trying right now
This post has been edited by Jdk412: Jan 11 2019, 10:33 AM
Go to the top of the page
 
Jdk412
post Jan 11 2019, 10:42 AM
Post#6



Posts: 21
Joined: 16-July 18



Simple mistakes seem to always get me dazed.gif

Thanks for the responses

Works perfectly notworthy.gif

Why does "And" have to be in the quotation marks?

Go to the top of the page
 
GroverParkGeorge
post Jan 11 2019, 10:43 AM
Post#7


UA Admin
Posts: 34,298
Joined: 20-June 02
From: Newcastle, WA


One should ALWAYS dim variables prior to using them. Include the datatype in that dim. See how Mike does it?

Another thing that is a really good idea is to refuse to accept the default control names offered by Access. Change the textbox control names to something that tells you more about the content of the control. For example, text115 ought to be renamed to something like, for example, txtDueDate.




--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Minty
post Jan 11 2019, 11:00 AM
Post#8



Posts: 174
Joined: 5-July 16



If you add the following you'll "see" what you are passing to the find command;

CODE
Dim strCriteria as string

strCriteria =  "[InventoryID]=" & id & " And [SalesOrder]=" & SO & " And [DueDate]=#" & Due & "# And [PartNumber]='" & PN & "'"

Debug.print strCriteria

rs.FindFirst strCriteria


See how the And fits in (press Ctrl and G on the debug window.)
This post has been edited by Minty: Jan 11 2019, 11:03 AM
Go to the top of the page
 
Jdk412
post Jan 11 2019, 11:00 AM
Post#9



Posts: 21
Joined: 16-July 18



Duly Noted!
I am self taught with only like 6 months of experience and tasked with building a ERP system so my head is constantly spinning
The hardest part seems to be syntax and concatenation.....
This post has been edited by Jdk412: Jan 11 2019, 11:04 AM
Go to the top of the page
 
GroverParkGeorge
post Jan 11 2019, 11:37 AM
Post#10


UA Admin
Posts: 34,298
Joined: 20-June 02
From: Newcastle, WA


It's no secret. Everyone here started at the same point. We knew absolutely nothing about Access until we started using it for important projects, so you're in excellent company among us.

An ERP system? IMO, one of the most complex applications one can tackle, so in that way you've dived into the deep end, as well.

Don't get discouraged. It could, and probably will, take many months to get this project to a fully functional state, although parts of it may be usable sooner.

IMO, the HARDEST part is actually designing a valid table structure. If you have relational database design concepts down, you're a long way already.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Jan 12 2019, 05:58 AM
Post#11


UtterAccess VIP
Posts: 10,478
Joined: 10-February 04
From: South Charleston, WV


ERP = Enterprise Resource Planning.

Actually, those are something you generally buy, not develop. Are you sure you're not implementing an ERP?

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Jan 12 2019, 06:30 AM
Post#12


UtterAccess VIP
Posts: 10,703
Joined: 6-December 03
From: Telegraph Hill


@Jdk412,

See this link on the UA Wiki for a good (essential!) way to eliminate many simple errors in your code.

hth,

d

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


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Jan 12 2019, 08:39 AM
Post#13


UA Admin
Posts: 34,298
Joined: 20-June 02
From: Newcastle, WA


Ironically, I DID work on an ERP for a client a while back (we parted ways for reasons unrelated to the project and I always wished I could have stuck around and finished it).

It was an Access FE with SQL Server BE and got to be quite complex. Unfortunately, the President of the company fancied himself a developer as well. He was constantly tinkering with "completed" forms, and trying out new ideas as we went. "Change is the only constant" could have been our motto.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th January 2019 - 02:58 AM