I tried to manipulate some code that was based on making two selections and then looking up the third. Now I have just one item to select and want my database to provide a lookup. I took the code (pasted at the end for reference) and came up with this: -
Option Compare Database
-
Option Explicit
-
-
Private Sub cboSize_AfterUpdate()
-
If Not IsNull(Me![cboSIZE]) Then
-
Me![txtCommodityNum] = DLookup("[BLANK_COMMODITY_NUM]", "tblBLANK_COMMODITY")
-
-
End If
-
End Sub
-
-
Private Sub cboClass_Change()
-
Call ChangeRowSource
-
End Sub
-
-
End Sub
-
The code actually fires without bugs (which is a huge accomplishment for me) but it returns only one commodity number no matter what I enter. So it's not working. I suspect my error is at the end of the longest line...it had ticks and quotes that I deleted and those things get me every time.
Also, I researched online before asking the question, and the internet kept directing me to a wizard. I'm way too far in my DB to invoke that. Relationships are defined, tables, queries, and forms are made, etc.
Thanks in advance to the generous person who helps me!
Just for reference, here is the wonderful and working code I began with. cboCLASS no longer exists in new DB. -
Option Compare Database
-
Option Explicit
-
-
Private Sub cboClass_AfterUpdate()
-
If Not IsNull(Me![CBOclass]) Then
-
Me![cboSIZE].RowSource = "SELECT [SIZE] FROM tblCOMMODITY WHERE [CLASS] = '" & Me![CBOclass] & "' ORDER BY CInt(IIf(IsNumeric(Right([SIZE],1)),[SIZE],Left([SIZE],Len([SIZE])-1)));"
-
End If
-
End Sub
-
-
Private Sub cboSize_AfterUpdate()
-
If Not IsNull(Me![CBOclass]) And Not IsNull(Me![cboSIZE]) Then
-
Me![txtCommodityNum] = DLookup("[COMMODITY_NUM]", "tblCOMMODITY", "[CLASS] = '" & Me![CBOclass] & _
-
"' And [SIZE] = '" & Me![cboSIZE] & "'")
-
End If
-
End Sub
-
-
Private Sub cboClass_Change()
-
Call ChangeRowSource
-
End Sub
-
-
Private Sub cboSize_Change()
-
Call ChangeRowSource
-
End Sub
-
-
Private Sub ChangeRowSource()
-
With Me
-
If Not IsNull(.CBOclass) And Not IsNull(.cboSIZE) Then
-
.txtCommodityNum = DLookup("[COMMODITY_NUM]", _
-
"tblCOMMODITY", _
-
"[CLASS] = '" & .CBOclass & "' And " & _
-
"[SIZE] = '" & .cboSIZE & "'")
-
End If
-
End With
-
End Sub
-
-
That's not quite what I was after Danica, but let me see if I can piece together what we do have and see if we can work from there. If I go astray then let me know where of course.
You are looking to find a record where some size field matches a size control on your form. There is only one criteria required and it must be selected in order to determine the commodity. From earlier posts I can determine (Again, correct if I misread.) that the control is called [cboSize] and the field is called [BLANK_SIZE]. You have also stated that you believe [BLANK_SIZE] to be a text field rather than numeric.
If all that is correct then the following code should do what you need : - Option Compare Database
-
Option Explicit
-
-
Private Sub cboSize_AfterUpdate()
-
Dim strWhere As String
-
-
With Me
-
If IsNull(.cboSIZE) Then
-
.txtCommodityNum = Null
-
Else
-
strWhere = Replace("([BLANK_SIZE]='%S')", "%S", .cboSize)
-
.txtCommodityNum = DLookup(Expr:="[BLANK_COMMODITY_NUM]", _
-
Domain:="[tblBLANK_COMMODITY]", _
-
Criteria:=strWhere)
-
End If
-
End With
-
End Sub
14 1869
Not sure why my [code] inserts aren't working like I'm used to.
In answer to your second post, it is probably because your end insert has the slash as \ instead of / which is what is inserted when you use the <CODE/> button.
In answer to your first post, you are correct that the error lies in your longest line. DLookup can only return one value and always selects the first record that it finds. Since your DLookup() function doesn't have any criteria, it will always return the value in the first record of the table. You need to add a criteria so that it will find the record you want and then it should work.
I feel like I am getting closer but still not there. - Private Sub cboSize_AfterUpdate()
-
If Not IsNull(Me![cboSIZE]) Then
-
Me![txtCommodityNum] = DLookup("[BLANK_COMMODITY_NUM]", "tblBLANK_COMMODITY", [BLANK_SIZE] = '" & Me![cboSIZE] & "')
-
-
End If
-
End Sub
Got any more advice? I'll not be shameful in admitting I don't fully understand ' " and Me! But I did look online for more help and I think I'm getting close. :-)
No need to be ashamed of not understanding. That is why we are here.
Try this in place of line 3: - Me!txtCommodityNum = DLookup("[BLANK_COMMODITY_NUM]", "tblBLANK_COMMODITY", "[BLANK_SIZE] = " & "'" & Me![cboSIZE] & "'")
I don't deal too much with text criteria like this, so I'm not totally sure on what is needed with the double and single quotes. Me! just refers to the current form. After the bang (the exclamation point), you have control names such as txtCommodityNum. This is instead of the full reference Forms!form_name!txtCommodityNum
I feel like I have read an article about the use of double and single quotes, but I can't find it right now. Hopefully an expert can get in here and explain it to you (and me :)
Thanks Seth. Not working yet. It's not calling the debugger, but it's not updating the field either. I know we're close. We can see if someone else's eye can catch it. I have plenty of time...I am always very grateful for the help I receive on Bytes.
Try this: - Me!txtCommodityNum = DLookup("[BLANK_COMMODITY_NUM]", "tblBLANK_COMMODITY", "[BLANK_SIZE] = '" & Me![cboSIZE] & "'")
I found a website that listed a DLookup with a text criteria and this is what their syntax was.
Seth, thank you again. Tried that with same result. Code is firing but not updating my field. I can follow that matching it to the original code. So I wonder if I deleted too much from the original code. I found a reference to cbo_Class in my first pasting of the code. I deleted that too; now I'm down to this: (Perhaps I have oversimplified this?) - Option Compare Database
-
Option Explicit
-
-
Private Sub cboSize_AfterUpdate()
-
If Not IsNull(Me![cboSIZE]) Then
-
Me![txtCommodityNum] = DLookup("[BLANK_COMMODITY_NUM]", "tblBLANK_COMMODITY", "[BLANK_SIZE] = " & "'" & Me![cboSIZE] & "'")
-
-
End If
-
End Sub
-
-
Private Sub cboSIZE_BeforeUpdate(Cancel As Integer)
-
-
End Sub
-
NeoPa 32,556
Expert Mod 16PB
Hi Danica. Feel free to give me a call. I'd love to talk again anyway, and I'm up for hours yet anyway ;-)
Onto the issue. First off, Seth seems to be doing a decent job. Especially as he's still still learning much of it himself. So far all the advice has been good.
Let me start though, by getting you to take a step back and to ask yourself exactly what you require. How many items do you want to filter on, and what type are they all? String values and numeric values are handled quite differently in SQL (and SQL is what filters are written in). Here's an explanatory link - Quotes (') and Double-Quotes (") - Where and When to use them. That further links you to other references related to filtering (or WHERE clause specification).
You also need to know exactly which controls in your filter criteria are mandatory and which are optional.
Only when we know all of that detail can we advise properly on what you need.
PS. The link explains all about the quotes. Me, is a special object reference found in all Form and Report object modules that always refers to the associated object. So, in Form_frmA's object module Me refers to Form_frmA, whereas in Form_frmB's object module Me refers to Form_frmB. I hope that explains it clearly.
Hi NeoPa! Can't call you tonight I'm afraid. It's the football national championship and my team is playing in the game! :-)
Oh, you have just reminded me how little I am. I don't know the answers to your questions but that has never stopped me before. LOL. On a brigher note, I do understand the Me explanation.
I *think* I am filtering on one item and nothing is numeric so I guess that means these items are strings. :-) You have never let my inability to answer your question stop me from getting the answer I seek. You have an amazing ability to keep on simplifying it until our intellects meet. Sorry you have to travel so far. LOL!! :-D
Hopefully we will talk soon.
Thanks to you AND Seth.
NeoPa 32,556
Expert Mod 16PB
Another night then Danica. I'll look forward to it, but I'm playing football tomorrow night myself (different football from yours I expect, but never mind). Good luck for tonight anyway. I hope your team wins :-)
As for your form, you refer in the OP to making two selections. I expect these are the controls we're talking about. What are the names of the controls, and the fields they are supposed to match? Are both necessary or could only one, or even neither, be selected in some circumstances. It seems they are both string values. That helps, but we need a little more before we can proceed.
We will be looking to produce a filter string that fits the following pattern : - ([Field1] = 'XXX') AND ([Field2] = 'YYY')
Sometimes not all of that will be necessary though, hence the greater details required.
I understand the confusion. Great news. I *think* this is going to be very simple for your great mind. ;-)
The original piece of code was from a more complicated DB and it had two filters. However I stole the code because I lack the ability to write it, but can dangerously manipulate it, and what I'm aiming for NOW is one simple filter. I want to choose a size, and generate a commodity number.
NeoPa 32,556
Expert Mod 16PB
That's not quite what I was after Danica, but let me see if I can piece together what we do have and see if we can work from there. If I go astray then let me know where of course.
You are looking to find a record where some size field matches a size control on your form. There is only one criteria required and it must be selected in order to determine the commodity. From earlier posts I can determine (Again, correct if I misread.) that the control is called [cboSize] and the field is called [BLANK_SIZE]. You have also stated that you believe [BLANK_SIZE] to be a text field rather than numeric.
If all that is correct then the following code should do what you need : - Option Compare Database
-
Option Explicit
-
-
Private Sub cboSize_AfterUpdate()
-
Dim strWhere As String
-
-
With Me
-
If IsNull(.cboSIZE) Then
-
.txtCommodityNum = Null
-
Else
-
strWhere = Replace("([BLANK_SIZE]='%S')", "%S", .cboSize)
-
.txtCommodityNum = DLookup(Expr:="[BLANK_COMMODITY_NUM]", _
-
Domain:="[tblBLANK_COMMODITY]", _
-
Criteria:=strWhere)
-
End If
-
End With
-
End Sub
Yes yes. Magic again! SCORE!!
NeoPa 32,556
Expert Mod 16PB
You've been watching too much football my dear (or should that be Danica ;-)). Anyway, I'm always pleased to help :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: lawrence |
last post by:
Can someone please tell me where I can get some open source code for
resizing images? I know such code has been written a million times and
I don't feel like doing it again from scratch.
|
by: Shi Jin |
last post by:
Hi there,
I am very new to C# and dot net. I am currently working on a web
service and a client. Now I need to change the default class
construction in the proxy code from
this.Url = "some...
|
by: HALLES |
last post by:
HELLO !
I seek to use a form too fill a file without putting a server on my
computer.
I am no C# programmer, i can do some things in Javascript but IT CAN T
WRITE REWRITE APPEND A FILE ON LOCAL...
|
by: Chris Saunders |
last post by:
I came across a construction in some code I was reading that I have
never seen before and hope that someone could tell me how to
interpret this:
int (*literalScanners)(const ENCODING *,
const...
|
by: Mark Friedman |
last post by:
Does anyone know if there is a way to get some code in an ASP.NET
application to run even before any HTTP requests come in for that
application? I have some timer based code that I would like to...
|
by: Michael |
last post by:
Hi Everyone.
I have been designing a form with about 100 or so controls and today I
pasted some code from another test project into this one and then all the
controls on the form disapeared from...
|
by: Andrew Kidd |
last post by:
I see this when I'm stepping through in the debugger ... just thought I'd
ask, and I just know it's going to be one of those "Doh" moments, but it's
got me foxed just now.
|
by: Ravi |
last post by:
Can someone provide Code for vCARD Parser in C language ?
I am in the need for vCard Parser which i can integarte on VC++
environment.
Thanks in advance
Ravindra Singhai
|
by: MalcR |
last post by:
I’m a bit of a drag and drop merchant, but this one can’t be solved using the wizards. Would someone be kind enough to point me to some code which will allow me to resolve the following:-
My table...
|
by: savage678 |
last post by:
Hi Everyone,
I am new to this forum and am i dire need of some help. I am trying to use wildcard searches in infopath. I have it connected to an access database using data connection.
I have...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |