Modules & VBA :: Inventory - Show List Of Items That Aren't Already In A Table

Jun 19, 2015

I have a list box that allows multiple selections [Inventory]. I also have a combo box that has multiple selections [Shows].

Right now, user selects from list box and from a combo box and clicks a button. On button click, the items from the list box are associated with the PK from the combo and stored in a junction table. This allows me to quickly associate many inventory items to one show.

I realized that there I currently have no way to prevent duplicate Inventory+show records in the junction table besides having a composite key. This would be fine except no records get inserted into the junction table if there's a duplicate entry.

Ideally, I think that the user should select from the combo box [Shows]. This should narrow down what shows up in the list box [Inventory] in a way that Inventory items already associated with the show are not displayed.

If I have 10 Inventory items and Inventory items 1-5 are already associated with Show 1; after I select the combo box, the list box only displays Inventory items 6-10.

Here's the associated code

Option Compare Database
Option Explicit
Private Sub cmdAddRecords_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control

[Code] ....

View Replies


ADVERTISEMENT

Modules & VBA :: Add Items Received To Inventory Table?

Feb 18, 2014

I'm trying to add items received to my inventory table. If the item is already in the table, I just want to update the number and cost etc. If the item is not in the inventory table I want to add it. My problem is determining which item in the source table is already in the target table so I can either update of add. how to find an item number in target table by looping through the source table?

Here's the code I've written which doesn't work.

Dim I As Integer
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Set rs = Nothing
Set rs2 = Nothing
Set db = CurrentDb

[code]....

View 5 Replies View Related

Modules & VBA :: FIFO Inventory Calculation - Cost Of Items?

Jan 29, 2015

I am setting up a inventory database and i will like the cost of the items to be calculated using FIFO.

Sample tables:

PARTS TABLE:
part code
description
cost
quantity on hand

PART TYPES
part type id
party type

INVENTORY
invent id
location
reorder quantity

INVOICE
id
date
part code
part type
qty
cost
ext cost

NB

For every item i will like the previous cost to be charged before the new cost. eg. if 10 pens where entered at $2 and another 5pens were entered at $4 each and 1 need 11 pens, i want the first 10 to be charged at $2 each and one at $4.

View 6 Replies View Related

Queries :: How To List Records That Aren't There Yet

May 15, 2013

I'm new to query design. I have a database where operators will enter readings from 14 different compressors, twice per shift, three shifts per day. In the Rounds table for each record the following fields are included:

Timestamp (includes date)
CompressorID (1-14)
Shift (1st, 2nd, 3rd)

Round (each set of readings is a round, each shift is supposed to do 2 per shift, the rounds are numbered Rnd 1 and Rnd 2)

Status (this is one of many readings they will record for each round/machine)

On the paper form it is easy for the operator to tell which machine they haven't recorded a round for yet because the space for it on the form is blank. In the Access form they are simply presented with a blank space to enter a new record. I'm trying to develop a strategy to make it easier for the operator to see at a glance which compressors they haven't entered readings for yet. Management also needs a weekly report of which rounds were done and which were missed.

I'm thinking of creating a datasheet from a query that will list the missing rounds for each compressor/round/shift based on the current date. I think I can put that datasheet onto the form the operator is using to record the readings or perhaps on a subform they can access with a button. I'm having trouble designing the query. So far I've created a query that lists all the rounds for a specific date, then another query that uses those results to list the compressors that are missing a record.

View 14 Replies View Related

Make A List Of Fields That Aren't Null

Nov 26, 2007

I have a table that has several fields that contain a date if a user is licenced to use a certain piece of equipment and is null if not. Each recor has other data in it such as name, and a unique identifier (numeric). What I want to do is make a list of the fields which have a date in them to create a licence for what they can operate. Any help on which way to go...pretty new at access.

View 14 Replies View Related

Modules & VBA :: Sum Of Items Selected In A List Box

Sep 10, 2013

how to get the sum of column 2 of a list box total bags is in the second column, i only want the total of bags of the ones selected

I can get the sum of all the boxes but only want highlighted ones

Public Function SumListBox(sForm As String, _
sCtrl As String, iColumn As Integer) As Variant
Dim frm As Form

[Code]....

View 1 Replies View Related

Modules & VBA :: Looping Through All Items In List Box?

Apr 5, 2015

I have managed to amend records based on the user selecting multiple items in a list box by using the following code.

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Usage", dbOpenDynaset, dbAppendOnly)
Set ctl = [Forms]![frmsearch]![lstSelector]

[Code].....

what I need to do now, is to loop(I think) through and amend all the records in a populate listbox, with no selections, similar to above, but without selection.

View 3 Replies View Related

Forms :: Show All Items On Table For A Particular Key

Oct 29, 2014

I have a form which when displayed shows all the items on the table (usually 1 or 2) for a particular key.

The following row is part filled in awaiting further input for the rest of the fields of that row.

I have a button (exit) which basically closes the form but it also validates the (potentially) partial field ultimate row. If the user hasn't added any data to the partially filled row then I delete that row with this code.....

If IsNull(Me.Field1) or IsNull(Me.field2) Then
....If Not Me.NewRecord Then
........ DoCmd.SetWarnings False
........ strSQL = "DELETE * FROM [Component Swaps] WHERE [ID] =" & [ID]
........DoCmd.RunSQL strSQL
........ Me.Refresh
........ DoCmd.SetWarnings Trye
.... End If

.... DoCmd.Close
.... Exit Sub
End If

This works. I have copied the exact code into the close event for the form but it seems like in that part of the code the previous row is being picked up.

So for example if I have 2 records on the table I display 3 rows (as 1 is created with partial data) thus

ID.....Prefilled Field......Field1......Field2
1......fromrow1............F1...........F2
2......fromrow2............F11..........F22
3......generatedrow3.....NULL........NULL

If the code goes through the exit event then Me.Field1 and Me.Field2 are Null and the record with the ID=3 gets deleted.

Going through effectively the same code for the close event Me.Field1 = "F11" and Me.Field2 = "F22" and the record doesn't get deleted (ID=2 as well)...

View 6 Replies View Related

Modules & VBA :: Items In List Box Deselected When Clicked

Jul 8, 2014

I have added a list box to a form with Single Selection mode on. Its purpose is to improve the interface. It contains items that represent all records. Clicking on the list box causes the form to jump to another record.

The problem is following: if I click on the List Box, it clears the selection and nothing is highlighted but it jumps to correct record. When I use standard record selection buttons, it highlights the correct items.

I read the index of selected item from .ListIndex property because Selected() does not work in a Single Selection mode. However, this is read-only property and I cannot use this to highlight the item back from VBA. But when I use Selected() it is not working. I mean when I click again on the same item it's selected. It's weird. I attached a simple database file with this problem.

The second problem is, when using standard record selection buttons, access iterates through all records and then jump to empty one. That is not like a new record. I don't know which event to use to control this situation. I would like to deselect all items, let user enter the data and re-query the List Box with a new record.

View 3 Replies View Related

Modules & VBA :: List Box Items And Date Range

Jun 17, 2015

I have a multiselect listbox and two date fields (StartDate & EndDate) in an Access form.I am trying to add records to the Table through the form on a button click.I select multiple items from the list box and the date range between the start date and end date will be equal to the items selected from listbox.For each item selected from the list box I need to add a separate record with a date.So the first record will have List box item selected1 and the start date.Next record will have item 2 from list box and date as dateadd("d",startdate,1)And final record will have last item selected from the listbox and date as enddate.

View 5 Replies View Related

Modules & VBA :: MDB File - Deleting Multiple Items In List At Once

May 28, 2014

In appendix is .mdb file with this thema.

Inside you can find one form with listbox (with multi selecting ability).

I use this code :

Dim strSQL As String
Dim i As Variant
With Me.se1
For Each i In .ItemsSelected
SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;"
CurrentDb.Execute (SQL)
Next
End With
Me.se1.Requery

Result is this one :
1) If I delete only one item in list, it is ok. I can do it again and again and it is working fine.

BUT

2) If I delete more items in list at once, it is ok - but if I will try to do it again then there is an error because .ItemData(i) value is Null.

View 10 Replies View Related

Modules & VBA :: Using Array To Store Selected Items In List Box

Apr 10, 2015

I want to use an array to store data from a list box into a variable. I want it to be able to store one value, or multiple values, depending on what is selected.

Main problem: this list box feeds off a table which has employee names and their e-mails. The list box itself only shows the names, and when I select what I want the array to store is their e-mails, not their names.

Code:
Dim strNames As String
Dim varItem As Variant
Dim intCount As Integer
For Each varItem In Me.lstNames.ItemsSelected
intCount = intCount + 1
Select Case Len(strNames)

[Code] ....

That code successfully displays the item I selected, but only displays the name. I need to make it look in the table and get me column #2. I also want it to be able to select more than one item at a time.

View 5 Replies View Related

Modules & VBA :: Text Box To Become Visible Based On Selected Items From List Box

Oct 13, 2014

I have the code below and am trying to have a form allow Text Box to become visible based on selected items from a List box. Why I am getting the error listed?

Compile Error: Invalid Qualifier

Code:
Private Sub specific_opt_Click()
Dim users As Control
Dim ctrler As String
Dim xx As Long
If Me.specific_opt = True Then GoTo 169

[Code] ....

View 8 Replies View Related

Forms :: Show Selected Subform Items In A Table Field

Dec 9, 2013

Given a subform that lists items:

a
b
c
d
e
f
g

Given a table that contains a coverage field

customer coverage
smith a, b, d, g

How would I create a relationship between a subform and a coverage field such that when i multi select items in the subform, it will show what items are selected in the coverage field as in the example.

View 1 Replies View Related

Modules & VBA :: Combo Box Will Display Specific List Of Items - Form Asking For Parameter

May 5, 2015

I have the below code behind a form so that a combo box will display a specific list of items based on the data in another combo box on my form.

I have two copies of this same form for two different departments. One of the forms works like a dream. However, when I copy that form, change the name, and update the code as pictured below, the form is asking for a parameter FROM MY ORIGINAL FORM and will not requery the combo box. I can't figure out why...there is no reference to the original form in my VBA as you can see below. I tried deleting the form and re-creating it, I tried deleting the code and re-typing it to no avail.

Private Sub cmboType_AfterUpdate()
Me.cmboAction.RowSource = "SELECT tblStatusList.Status FROM tblStatusList WHERE (((tblStatusList.Department)=[forms]![frmInquiryFraud]![cmboType]));"
End Sub

View 4 Replies View Related

Modules & VBA :: Multi-select List Box Items To Pass Into Text Boxes

Oct 16, 2014

I have an access project that I am working on and need to be able to select multiple items from a listbox and have the exact selections appear in a textbox on the same form. I have looked around and have not been able to find any code that works.

I have tried:

Me.user2 = Me.slct_auditor.Column(0, 1)
Me.user3 = Me.slct_auditor.Column(0, 2)
Me.user4 = Me.slct_auditor.Column(0, 3)
Me.user5 = Me.slct_auditor.Column(0, 4)
Me.user6 = Me.slct_auditor.Column(0, 5)
Me.user7 = Me.slct_auditor.Column(0, 6)
Me.user8 = Me.slct_auditor.Column(0, 7)

but when skipping the first item in the listbox it is still passed as into the textbox.

View 4 Replies View Related

Queries :: Tables With Inventory Items And Storage Charges Per Day - Multiplication Expression

Mar 13, 2014

I have a table with inventory items, a separate table with storage charges per day (ex .03, .04, .05 per day/per item)

I have created some queries where I take the items & # of days they have been in storage and when I try and create an expression for storage charges based upon QTY & # of days I am getting results like it is multiplying whole numbers and not very small increments like .03

I have checked the math, and its not multiplying by 3 instead of .03... I cant quite figure out how it is coming up with the numbers.

Again, the pricing is coming from a lookup wizard to another table. It seems like it should be a very straight forward expression but I cannot get it to work. Does the figures being from a lookup have any issues?

View 6 Replies View Related

Multiple Items Selected From List Box Through A From Not Passes To The Table

Nov 23, 2006

Can some one tell me why, on the attached database example, I can select multiple items from list boxes
"TipoCliente" and "FaixaEt" through "frmClientes" form but they are not passed forward to table "tblClientes" ?

Try to onpen attached database example and use form "fmrClientes" and select multiple items at
"TipoCliente" and "FaixaEt" list boxes by entering them with mouse click and Ctrl key pressed .
Select also single item from
"NivEns" Combobox and write anything on "Nome" and "Apelido" fields .

Go to the table "tblClientes" and you can see text fields "Nome" and "Apelido" and also the item "NivEns" from Combobox "NivEns" are all there but not the items you selected from "TipoCliente" and "FaixaEt" list boxes .

What is happenig ?

Lots of thanlks

Miguel

View 2 Replies View Related

Modules & VBA :: List Box To Show Recordset With Record Count

Sep 22, 2013

I have the listbox named "payment_date", which shows recordset specified by following code:

Code:
Private sub Form_Current()
Dim que As String
que = "SELECT [date] FROM payments WHERE id Like '" & Me!myid & "*' ORDER BY [date] desc;"
Me.payment_date.RowSource = que
End Sub

I need my listbox to show recordset with record count like this:

3.date
2.date
1.date

View 4 Replies View Related

Modules & VBA :: How To Get List Of All Users In Active Directory - Show CN Name

Oct 10, 2013

I would like to get a list of all the users in the active directory and put them in a combo box. How to do this? I would like it to show there CN name

View 9 Replies View Related

Modules & VBA :: Show Hidden Tabs Based On List Box Column Value

Mar 30, 2015

I am designing a contact database for a diocese and the contacts record form is divided into several tabs, some of which are hidden by default. One of the controls on the form is a listbox (lboRoles), where a user can add one or more roles to contacts. The listbox has a hidden column that defines the TabIndex for the assigned role, and my goal is to make the associated tab on the form that was previously hidden, to now be visible.

For example, if a contact is assigned the role "Committee Member" and the tabindex value for that role is 3, the form should make the hidden tab (where the page index is also 3) now visible.

View 13 Replies View Related

Queries :: Products Form - Listbox To Show Recent Inventory Transactions

Apr 3, 2014

I have a products form, we are a manufacturing company, with a listbox to show recent inventory transactions. This is based on a query which shows all transactions with the current part id, and that all works well and fine.

The problem is, I would like to limit this query to show only the last 10 transactions in the listbox and not make it editable, ie not enabled. I set the show only in query design view to 10 and it says in the sql statement select top 10, however, the listbox consistently shows all related records. What am I missing?

View 8 Replies View Related

Modules & VBA :: ActiveX Control Events Aren't Raised While Calling Its Methods From The Open Event

Oct 9, 2013

We have an ActiveX grid control, 10Tec iGrid, written in VB6. One of its method, Group, used to group rows, raises the AfterAutoGroupRowCreated event so the developer can adjust the look and contents of every group row created during the automatic process of grouping.

When our ActiveX grid is hosted on an MS Access form and we call the Group method while populating the grid in the form's Open event, the AfterAutoGroupRowCreated event isn't triggered. But this event definitely works as expected in other development environments, and even in MS Access if we call Group from the form's Load event.

Is it a well know issue of MS Access, when any events of ActiveX controls aren't triggered while "executing" the Open event

View 1 Replies View Related

Modules & VBA :: Load Specific Email Items To Access Table

Jul 8, 2014

In my Access app, I need to be able to retrieve a specific email by date/time, then take the Subject, the Date received and body of that email and save it to a column in a table.how to do this in Access VBA?

View 1 Replies View Related

Queries :: Show Items Only Once

Nov 26, 2013

I would like to show all linked components (once) in a query.

With inner joins (option 2 when double clicking on a relationship line), it shows all linked components but with duplicates.

When selecting option 1 (only include rows where the joined field from both tables are equal) I only get 1 row due to the fact that not all components have values in all columns.

Therefore, How to create a query that shows all components that have one or more links to other parts. These links have be checked 3 tables.

I attached a overview of the tables involved.

View 12 Replies View Related

Using Combobox To Show Only Remaining Items?

Sep 18, 2015

I want to use combo box to select the resources to assign, but want to only show the remaining item, each time I click for new record.for example staff allocated for different jobs.

seat assigned to each person, so each time the free seats only be displayed in the list, Not the already assigned.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved