The VBA code I have at the moment:
me.Results.Rowsource = "SELECT car, title, FROM dbo_inventory"
Is it possible to add a variable to a specific row in listbox using the code above? In this case the price?
I would like to use a listbox set to multiselect to add records to a junction table. I've been using code to accomplish this with checkboxes (love how it looks and works) but after moving my tables to Office 365 as the backend, linkedto a local frontend, sql does not like this particular set up, and I do not have the time or knowledge to sort out why. So what I need is a step by step to look at the many, in this case possible roles a contact can have, and choose one or more, which then creates a record in the junction table with the contact id and role id.
I would prefer to not use a combobox on a continuous form because every time a user goes to select roles he would have to scroll through all the choices for each separate role.
I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] )
SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], *
FROM TestBank
WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
I am using Access 2013. I have the ability to pull a selection from a listbox. I can create a Select Sql string using that variable
sql As String, strCompany As String, strWhere As String
strCompany = strCompany & Me.lstResource.Column(0, varItem)
strWhere = "[Company name]=" & "'" & strCompany & "'"
sql = "select * FROM tblResources WHERE " & strWhere
From here I have trouble. I see lots of examples to run an active query but not much on a select query. I have tried a number of things with no success. How to use this select statement to actually run against an existing access table? I am not putting it into a form or report at this time, just running the query to check results.
I have to make a multi-user manage database.All the usernames are sorted in one table and exported to listbox in Form.For each user there is a separate table with 3 columns where i must fill information from listboxes.The listbox (there are 4 listboxes, one for users select and 3 for different parameters) are located in MainForm where you select the information.My question is how can I select a specific username(table) from the listbox and fill the information from the other listboxes to the selected one?
The usernames table is named - Clients
each table for separate user is named client1, client2, client3 etc.
the form where you choose what to select is named Fill form.
The listbox for users is named - List71 And the listboxes for parameters are named - List75, List77, List79 Also there are 3 textboxes with calculated fields that i also need to insert in the specific user table.
I have three forms: Form1 that contains subform1 and Form2 which is totally separate. Form1 contains a listbox that I use as a search form. When a record in the listbox is clicked, it populates Subform1 with more details.
If I am in Form2, I would like to be able to click a record which opens the listbox in Form1 to the same record (this will automatically update subform1 with the details).
I am using the Filtering a listbox method from this post [URL] .....
It works great apart from when i type too many characters and no search results can be found i get a run time error '2105' you cant go to the specific record.
I think it may be because my form has a row source. When the example uses an unbound from?
The following code gives me runtime error message "couldn't set the list property , Type mismatch".
Private Sub UserForm_Initialize()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws1 As Worksheet
Dim j As Long
j = 0
[Code] ....
I want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
View 3 Replies View RelatedI am trying to deny users from logging on from multiple pc's. Here is how i've got it so far,created table tblLoginLock with fields EmpID and LoggedIn both numberical data type created a form to set the Empid with tempvar!TempEmpId and LoggedIn as 1 when a user logs in.When he clicks the logOff button he is signed out and a form opens up and closes to set the LoggedIn field to 0.Now whenever a user is signed the LoggedIn Field is checked with this expression
Code:
DLookUp("[TempVars]!TempEmpID","tblLoginLock","[LoggedIn]=1")
if there is a one then the user is logged in the condtion is met and a message box pops up to warn the user that he is already logged in.
The above code does not check the specific TempVar to the LoggedIn field, the LoggedIn field is randomly checked for a '1' meaning if there is a '1' anywhere in the table then the condition is met and the user denied. how can i set the condition to validate the logged in user to the LoggedIn field.
I have two tables and two separate forms. [ID] is the link between them. (I cannot do a subform in this case).
Form1 displays 3 records
Have control button that goes to Form2 where [id] = NumID (NumID is "long" variable)
Have control button on Form2 to go back to Form1. It needs to go to the same record it came from. I don't want use a where clause (e.g., where [id] = [id]). This works, but the form1 is now filtered. I need all records available in Form1, but I want to go to the record it was on when the button was clicked to go to form1
Example:
Form1, record1, ID=30
Form1,record2,ID = 35
Form1,record3,ID = 40
Select record3,ID=40, then click on button to go to form2 (has where clause where ID=40).
Form2 is filtered and is correct.
Click on button to go back to form1. No Filter. It goes to 1st record (record1,ID=30). I need all records displayed, but need to be on record2,ID=35.
I have defined a recordsource based on a predefined query. Now I intend to use the SQL statement which works to produce other recordsources based on other queries. More concretely here is my code as of now:
Code:
Dim J1 As Long
Dim SQLRecordSource As String
J1 = Forms(ParName).MemID
SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _
"INNER JOIN [ActiveMembersQy] ON " & _
"[MembersTbl].[MemID] = [ActiveMembersQy].[MemID] " & _
" WHERE [ActiveMembersQy].[MemID] = " & J1 & ";"
In the future instead of writing it like this:
Code:
Select Case QryName
Case "NonMembersQy"
SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _
"INNER JOIN [NonMembersQy] ON " & _
"[MembersTbl].[MemID] = [NonMembersQy].[MemID] " & _
" WHERE [NonMembersQy].[MemID] = " & J1 & ";"
[Code] ....
I wish to write it like this (which does not work):
Code:
SQLRecordSource = "SELECT DISTINCTROW MembersTbl.* FROM MembersTbl" & _
"INNER JOIN QryName ON " & _
"MembersTbl.[MemID] = QryName.[MemID] " & _
" WHERE QryName.[MemID] = " & J1 & ";"
Is this possible ie put the name of the variable that holds the name of the query in quotes or sometimes put the name of the query instead of the contents of the name. If there is a contents function for variables in VBA that can be used by I doubt whether it exists.
is it possible to name a field in a query or a table with a variable name?
For example:
I want to choose in listbox "fieldname" the name of the field.
And the query field name looks like:
Code:
forms![testform]![fieldname]: FieldOfTheTable
Unfortunately it doesn't work.
Also if I save the choosen fieldname in a vba function.
Code:
fieldNameFunction(): FieldOfTheTable
This doesn't work, too.
I want to use a variable from a form to a query as a criterion. but how?
View 1 Replies View RelatedI am trying to create a table to show the parent/child relationships between assemblies, sub-assemblies and detail parts. The table I start with has 2 fields a "Part Number" and an "Indenture".
Please forgive all the periods, couldnt think of how else to seperate the values to depict the tables.
Example:
IND..PN
00....A
01....B
01....C
02....D
03....E
03....F
01....G
00....H
01....I
02....J
I can get the first step to completeing the new table of parent/child relationship like this:
PN....00....01....02....03
.A.....A
.B....."".....B
C......"".....C
D......"".....""......D
E......"".....""......"".....E
F......"".....""......"".....F
G......"".....""......""....G
H......H
I......""......I
J......"".....""......J
Where "" are null values
My problem is making it so that every part number has its own array, aka [family tree], so that for part "F" the table would hold "A,C,D,F"
I though an update query where for example:
*"Zero" is a single field string.
IIF([00]<>null(), Save [00] to Zero, Write Zero to [00])
Would work but have been unable to find any information about passing a field value to a variable for later use or comparison. I would have already done this in excel if the table had less that 65000 records.
Example of final table:
PN....00....01....02....03
.A.....A
.B.....A.....B
C......A.....C
D......A.....C......D
E......A.....C......D.....E
F......A.....C......D.....F
G......A.....C......D....G
H......H
I.......H......I
J.......H......I......J
Any help is greatly appreciated.
BsK
I have an append query that takes an item (once selected) from a listbox and it appends the contact name to a new table. Is there a way I can make an append query thay takes the ContactID and the ContactName and append it.
The listbox box has column 2 Bound (ContactName) soeven though I have written the query to take the ContactID it still only takes whats in the bound column!
I have a trouble to save data from Listbox to table. I can select mutiple data from Listbox, but the data cannot be saved on the table.
Here is the detail infor:
FORM name: Attendance
FORM record source: TBL_Attendance
Listbox Name: ARR_Result
table for Listbox to use: TBL_ARR
I want to these multiple records which come from Listbox (data on the TBL_ARR table)to be saved in table TBL_Attendance.
I have a form called "Record Call" when a user can input the calls that they have made or are scheduled. This form is based on the table "Call Records". I have created a new table called "CallAttendees" and added a listbox to my form for users to select who attended/participated in the call.
My code loops through the selections and writes these selections to the "CallAttendees" table. In this table i have an auto #, CallRecord, and Attendee fields. My code is supposed to write the "ID" field from the "Call Record" table to the "CallAttendees" table so that i can create the relationship between the two tables. However, this field is coming up blank since my form isn't generating the auto # until the record is closed.The tables are stored in a SQL server. I also tried saving the record prior to running my code.
I have a form with a listbox that allowed multiselection.
The listbox depends on the field "name"
And I have a table "names_should_be_selected" (fields: name, date ...)
It would be great if alle names in the listbox are selected when the name is in the table "names_should_be_selected".
I have a form where I do a search and find the correct auditor in the listbox from the AuditorTable. This form is opened from a master form where I have the same fields in the table (ClientTable). What I need is that when I find the one, I double click it and the records from that auditor is passed to the client table.how to use Dlookup.
View 14 Replies View Relatedi have a list box that fills based on the following code.....
Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL
Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL
There is another field in the [Client ProdVend] table called ID. I want to be able to select a product in the listbox, but have that selection open up a form based on the ID field associated with that product. Right now i use this.
DoCmd.OpenForm "ProductDetailsEditor", , , "Products='" & Me.List91 & "' AND Client_Account_Name='" & Me.Client_Account_Name & "'"
The Problem is if there are multiple products with the same name, instead of going to the specific instance of the product(cased on the ID)...it just opens all of the products with that name up, starting with the first one.....
I have a parent form which has a class variable (class module instance) to store the form' status and more.... and when i add a new record to the subform it resets the class variable field' data. but this only happens on first transaction, but if i re-run the steps (re-set the variable field value) it's not happening again.
View 3 Replies View RelatedGood morning,
I have a listbox that contains dates (0105, 0205, 0305, etc.) and depending upon which date is selected in the list box, I want to import the corresponding month's text file. Here's the code:
Private Sub List64_DblClick(Cancel As Integer)
Dim frm As Form, CTL As Control
Dim VARITM As Variant
Dim X As Integer
Dim period, PERIOD1 As String
period = Me!List64.Column(4)
PERIOD1 = Me.List64.Column(0)
DoCmd.TransferText acImportFixed, "df import specification", "LOCAL DF-SL", "S:FinanceAccounting OperationsNational AccountsAccount Recs116406-Account ReconDF_" & PERIOD1
DoCmd.TransferText acImportFixed, "Rf import specification", "LOCAL RF-SL", "S:FinanceAccounting OperationsNational AccountsAccount Recs116406-Account ReconRF_" & PERIOD1
DoCmd.TransferText acImportFixed, "MDdf_import_spec", "LOCAL DF-GL", "S:FinanceAccounting OperationsNational AccountsAccount Recs116406-Account ReconMDDF_" & PERIOD1
DoCmd.TransferText acImportFixed, "MDRf_import_spec", "LOCAL RF-GL", "S:FinanceAccounting OperationsNational AccountsAccount Recs116406-Account ReconMDRF_" & PERIOD1
So I have the files saved on the above mentioned S: drive, i.e., MDRF_0105 and when the user selects 0105 in the listbox, I want the appropriate file to be imported.
This code is set on the DblClick event.
Please help!
Thank you!
I have a table of customers and have table of stuff they can buy. Those two tables I use in the next table where their ordes get registred. I have and order form where I have a listbox. When I create a new order I want to have the customersname in a listbox but I want the customersID(primary key) returned in order table. How do I do that? :confused:
View 1 Replies View RelatedI have a form that has a control to search the table for a specific record See code below that is on the AfterUpdate event of a Combo Box
Code:
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Code]....
What I need to know is how to make the rowsource based on the filter and not the table.
I am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1
FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION
WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate()
'Dim LocQryStr As String
'Dim r As Integer
'Dim ctl9 As Control
'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.