Modules & VBA :: If Statement That Looks Up Values From One To Another Form
Aug 2, 2015
I have an issue that can be described as follows:
There is a textbox on a form and when I fill in its value, I would like it to lookup whether that value matches any of the values in another form and if it does present a message box. Both fields hold only numerical data, so no type mismatch issues.
I've tried the code following, but it only looks up the first value on the second form, not its entire dataset, which is problematic.
Code:
Private Sub Lot_AfterUpdate()
DoCmd.OpenForm ("Pot_Pot_ExtraLots")
If Me.Lot.Value = Forms!Pot_Pot_ExtraLots!ExtraPotLots.Value Then
MsgBox "There is a bag with extra sherds found during other analyses from this Lot! Lookup and combine results!"
DoCmd.Close acForm, "Pot_Pot_ExtraLots", acSaveNo
Else
DoCmd.Close acForm, "Pot_Pot_ExtraLots", acSaveNo
End If
End Sub
I'm now trying to speed up data entry within my database and have hit a brick wall with one part.Basically, this is within a form (for 'clauses') of which there is a one-to-one relationship with 'the Applicability' table (a series of fields with 'Yes/No' values for each and a related key field) - i.e. for each clause there are a series of circumstances when it will apply. I've set this up to create a record when one doesn't already exist using the default values (i.e. all applicable).
The method to speed up data entry is to have a pop-up form with unbound fields to list common Clause fields - including 'Applicability' as a subform. When entering a series of Clauses you tend to find they have the same 'Applicability' as their neighbour, so I would like to create a corresponding record in the Applicability table with the values set on the 'ClauseQuickAdd' form (Technically the 'ApplicabilityQuickAdd' sub form).I've edited an existing code within my database to provide two global variables (strAppFieldList and strSubAppFieldList) to put into the following statement:
Bringing up Error 2498 "An expression you entered is the wrong data type for one of the arguments". So I presume that I haven't quite got the format right for transferring yes/no values (strSubAppFieldList). See below for an extract of strSubAppFieldList:
Hey there! I've been searching about this forum and found a lot of threads on this sort of issue but haven't been able to figure it out from those alone, so feeling a bit useless :o long winded explanation by the way... I'm useless at explaining! :D
Well basically I have a query that is supposed to power a flexible search form, where you choose the fields you want to search by and enter your criteria. The form contains labelled fields with checkboxes next to them, which you tick if you want to search for something in that field. The results are displayed in a subform which reads from the query.
The problem is with the query! My solution seemed to work fine when I put my criteria in just one field when testing (that is the fields in query design builder, not the form), but when I applied the same to two fields it didn't. Instead, it would return nothing unless I specified exact and correct criteria for BOTH fields I had set up with my solution. So whereas before I could tick "Customer" and search for a customer account number and it would display ALL relevant records, now if i just ticked "Customer" and entered the same number, it would display nothing, unless I ALSO ticked "Booking" and entered a correct booking number that matched a record for that customer in the tables.
I've heard iif statements don't do too well in the criteria fields in the query design builder?? But I know nothing about using SQL really.
For example for the invoices field have used: IIf([Forms]![frmFindInvoices]![ChkInvNo]=True,[Forms]![frmFindInvoices]![InvNo])
So: IIf(form checkbox = true, form text box value)
However it seems to think that if the checkbox is not ticked, I want it to only display EMPTY invoicing fields. This is never the case and so no records are shown! If the tick box is not shown, there is not supposed to be criteria for this field, in other words it should allow ANYTHING through.
What I don't know how to do is tell the bloody thing that if the box is NOT ticked, it is NOT to check for anything in this field, because the user does not want to specify any criteria for this field! Things like "Is Null" produce the same results, probably because the query still thinks I want it to find records that have empty fields.
Any suggestions to specify to the query what I want? I really need to get this done! Thanks! :)
I have a subform that is used in multiple other forms. I wish to have a command button that will close whichever main form the subform is in. Is there some way I can make an if statement that is something like the following?
If (main form = A) then Close (form A) ElseIf (main form = B) then Close (form B) etc. End if
Unfortunately the button has to be in the subform, not the main form (which would be easier). The purpose of the button is to delete the current records that are open and then close the form. The catch is that the subform is the parent record to the main form, so the command has to originate from the subform so that the cascade delete works properly.
I've got a form, user inputs a whole bunch of data and the data is stored into 2 different tables (using a form and subform), however i also need to copy a few fields from the subform into another table (as a new record) the table i intend to insert this data into (tblAdjustments) has four fields (that im interested in) Type, Reason, Quantity and Product (not the exact names) Type and reason are both just straight strings and will be the same each time, regardless of what the other two variables are, so i need type to equal "Finished Goods" and Reason to equal Produced further to that, the subfrom has 2 fields, batchPackedQTY (which will be quantity in the new table) and fgID (product in the new table
so i need an insert into statement that does this, ive tried INSERT INTO tblAdjustment (adjType, adjReason, fgID, adjQTY) ("Finished Goods", "Produced", Forms!SubFormBatches.fgID, Forms!SubformBatches.batchPackedQTY)
I am trying to create an INSERT statement from a form to put unbound fields in a table. The challenge that I am a getting is that I am getting a
Run-time error '3075' Syntax error in date in query expression '#'
What is really perplexing and perhaps something that may guide in identifying the culprit is that I have an identifcal form that uses the identical code and it works.
Here is the code below:
Dim strSQL As String Dim strCriteria As String strSQL = "" strSQL = strSQL & " INSERT INTO [tblTicket]"
I would like passing values from first form until third form.
In the first form I have a list box after selecting items (For each selected item in first form I have 4 values) and pressing button (or right click of mouse) the second form will be open, then in the second form I have 2 option (inserting, deleting), when I select inserting or deleting in the second form, third form will be open, in the third form there is a "OK" button, when I press that, passed values from first form will be used for inserting or deleting records to the table.
I am trying to create an IFF statement in a query that if the field HRID is populated and the space category field equals the wrong space type than my results should be "Check Occup". I have tried several combinations but I still cannot get this to work.
=IIf([HRID]>"" And [SCat Text]="BRR_Restructuring" Or "Building_Common" Or "Floor_Common" Or "Vacant_Available","Check Occup")
Can I generate an IIf statement with two-true values? I have these two statements but don't know how to combine them:
IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;””) IIf((Weekday(date);1)=vbSaturday or vbSunday;y;””)
I have at field with a date and will like the true statement to be “x” if the data is Monday-Friday and “y” if the date are a Saturday or Sunday and if the date field is blank, I want to have a blank field here as well.
I have tried:
IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;y)
I have a bit of code that uses controls on a form to batch add records to the tables. Before the code executes, it needs to do a check to make sure that all of the needed data has been supplied.
Code:
If IsNull(txtDateAdded) Then MsgBox "Please supply the date that the Label record was created" Exit Sub Else If IsNull(cboRecordCreator) Then
I have a form that has a combobox with table names in it. I want to execute a very complicated SQL statement but I want to put the table name (from the combobox) into the SQL statement. I'm planning on making a button to do this, but I don't know how to put the SQL statement in VBS which would use the combobox value as the table name in the SQL statement.
I have a form with couple of textboxes bound to a table. When the user opens the form to enter records, i want to write a function that would go through the textboxes to check whether the textboxes are left NULL. Now i can write code on button click for each form, but i was wondering if its possible to write a function that could be called for each form that i have to check for null values.
I am trying to use a list-control on a form to let the user select multiple values. I have understood that this requires some VBA-code to step through the selections in the list, since the "multivalue-selection" is set to "Extended".
When I try to execute the code I have (found and have tried to adjust), then I get the error message "Object required". The "ListCount"-paramater always only results in a ZERO-value, when i step through the code:
Function cmdOpenQuery_Click() On Error GoTo Err_cmdOpenQuery_Click Dim MyDB As DAO.Database Dim qdef As DAO.QueryDef Dim i As Integer Dim strSQL As String
I have an unbound form in which a user enters data relating to a credit dispute, and when they hit the Save button I have a sub that is intended to insert that data into a table. This is a bit out of my comfort zone, so I found some code from another forum and adjusted it to my needs. However, I'm getting errors.
The table name is tbl_complaints. The fields from the form are:
Private Sub cmdSave_Click() Dim StrSql As String On Error GoTo ErrorHandler StrSql = "INSERT INTO tbl_complaints " _ & "(ACCT_NUMBER, CLIENT_NAME, EOSCAR_TYPE, EOSCAR_CONTROL_NUMBER, METHOD_OF_RECEIPT,
[Code] ....
The error I get is error number 3075, and it says I'm missing an operator in the section that references cboResponseType, but I can't seem to figure out what I'm missing. My head is spinning from staring at these parentheses and apostrophes for so long.
I am making a dp for end of day sales, and i want to compare data in a table to the numbers inputted in a form and see whether this value is higher or lower. the table displays the target sales figures for each day, and the form allows the area mangers to enter in the current day figures.
I'm trying to write some Code with the target to take a value from a textbox (out of a form) and a pregiven value (in this case = 2) and enter them in a new record in a table. But only if there is not already a record with the exact same combination of these to values. When done, the same button should proceed you to the next form (but this code is not already implemented in the fragment below) even if no values are added because they already exist in this combination.
Code: Public Function GetID_PatientStudiesGroup&() GetID_PatientStudiesGroup = [Forms]![frm_PatientStudiesGroupTZP]![ID_PatientStudiesGroup] End Function Private Sub BPRS_T1_Button_Click() Call GetID_PatientStudiesGroup& Dim strSQL As String
[Code] ....
As you can see, I tried to use a function to refer to the value out of the form, because I didn't find out how to refer to a form in an SQL Code.
Just to give you a better overview: The table where the information should be added is named "tbl_PatientStudiesGroupTZP". The form where you find the Textbox "ID_PatientStudiesGroup" (with the value I need to transport) is named "frm_PatientStudiesGroupTZP". Every part (except the WHERE NOT EXISTS part) worked perfectly for itself, but not when thrown together.
I have hit a roadblock trying to send data to excel. I have created a table of Arrays where each record has an ArrayID, a record ID, an X unit and a Y unit. I want to be able to pick an ArrayID from a form and send the corresponding X and Y values to excel as part of a Linest calculation.
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
I am building a tracking database where we would be able to track information which field are null data and report them to our Administration to fill the null data. For this I have created a form name "Search" and I have a combo box control on that form which is bound to "Table = employee" and its ROW SOURCE TYPE="Field List", I would like to able to query records where the selected value in this combo box is null through out the table.
For example if I select "Telephone" from this combo box dropdown, I would like the query to show all the records where the "Telephone" is null, how to set the criteria in query to take the combo box value as "Field Name" and then compare it with the Field/Column in the table and show the null values.
I have a lookup value in a table, and I would like to remove all values of the lookup value from vba. How can i do it via vba? i've been trying to do it in a query but i have always some errors and i can not remove them.
I am having some issues trying to use both and & or in a If statement.
The code below is what I am trying to do and it is not working.
Code:
ElseIf (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "Expired") Or (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "No longer eligible") Then MsgBox "Please enter Associate Name" Me.Date_authorization_expires.SetFocus
I tried the code below and it works but as soon as a I enter the Or part it will not work.
ElseIf (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "Expired") Then MsgBox "Please enter Associate Name" Me.Date_authorization_expires.SetFocus
I am trying to write a statement and it returns: #Type!. I know what this means but it should not give this error.I have button that copies a certain number of fields to the clipboard. The one below is one of them. Here is the coding I am using that works:
Code: =Trim(IIf([CallingContactRel] Is Not Null,[CallingContactRel] & ": " & [CallingContact],[CallingContact]))
I would like to add the following condition to this code. I have a yes/no field called BookingNoteContactExcl. If this is ticked, it should exclude [CallingContact] from being copied, in other words, return "" in the code above.
Using Access 2010. I use the following code to search for a client based on ClientID. ClientId is an Auto Number, Long Integer, format is 00000. The code works fine except if I type in the leading zeros for a ClientId that does not have at least a 1 in the first digit. For example it will not find client 00100 but will find client 10001.Is there any way of setting the Dim statement to look for a ClientID with a leading zero?
Private Sub cmdClientIdSearch_Click() Dim rs As Object Dim strCriteria As String strCriteria = InputBox("Please enter Client ID" ) If strCriteria > "" Then
I have the following code on an After Update event:
If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" Then Me.ProgPriceTxt.Value = "85" End If
This works fine. When I add another "And" to the statement, however, it no longer functions:If
Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" And Me.PavRentCheck = False Then Me.ProgPriceTxt.Value = "85" End If
Is it possible to put three conditions into an And statement? This thread seems to imply so (it's a different situation, but it seems close enough).
I believe it doesn't have anything to do with my text boxes or fields because this same issue has occurred in other places when I tried to have three conditions in an And statement.
