Use Record Number For Item Numbering In Subform
Sep 22, 2004I want to use the record number to automatically number items entered into a subform. Anybody know how to do this or if it can be done?
View Replies
ADVERTISEMENT
Modules & VBA :: Invoice And Subform - Add Multiple Record (Item) At Once
Mar 23, 2014I almost complete my mini project - Stock Inventory:
To track Items enter and exit from Videoshop or simply known as Stock Transaction (IN/OUT). This also include a Inventory Catalog which basically show all the Items in the Videoshop, each Items details including category, transaction type description and most importantly quantity on hand.
As mentioned above, I almost complete the database except the most important Form - New Stock Transaction where User enter all the necessary details to issue an IN or OUT Transaction. To make things easy to understand, it share many similarity with Invoice and New Order.
Code:
Main Form (Table: StockTrans)
Trans_ID
Trans_Type
Trans_Date
Trans_Cust
Trans_Note
Code:
SubForm (Table: StockTrans_Items)
Trans_ID fk
ItemID fk
Trans_Item_Qty
When I finished records entry and enter Invoice completed button then it will auto update the Item Quantity on Hand.
Populating A Subform Item To Another Subform Item.
Dec 21, 2006Hey all.
I'm trying to get a combo box to populate the box next to it.
Basically pick a project number in the 1st box and have the project name show up in the second box.
I have it working, but the problem is that when it puts in the name of the project, it fills the whole column with that name, and not just the one box.
Here is what it's doing:
http://img156.imageshack.us/img156/2659/dbhm4.jpg
Here is the code im using.
Private Sub ProjectNumber_AfterUpdate()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
ProjectNumber.SetFocus
If ProjectNumber.Value > 0 Then
strSQL = "SELECT * FROM ProjectList WHERE ID = " & ProjectNumber.Value
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
Me.ProjectName = rs("ProjectName")
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End If
End Sub
Let me know what I can do.
Thanks!!
Forms :: Incremental Numbering From Existing Number Sequence
Aug 28, 2013I have a Primary Key field named Member ID(Number format) in a Table named Mail List. The field is populated with existing ID numbers and my need is to use my Member Entry Form to increment by 1 from the last entry.
I have gleaned from other references that this can easily be achieved by the following entry in the Default Value of the property sheet for the relevant field;
=DMax("Member ID","Mail List")+1
The Form saves without error but when I attempt to add a new record in the entry form, the ID Number reads "#Error".
Reports :: Selective Subform By Record Number?
Jul 2, 2013I have a DB with 1000+Records. Each record has a "History" field showing the date I last visited (about 20+Visits each). When I re-visit I print Report (A4 size) which gives me the address and previous visits, however I only want my report to show the last 6 visits.
I have tried both a Query & Subform and changed the "Top Values" from ALL to 6, but this only gives the last 6 visits from all records. I need to see the last 6 visits for each record.
Numbering The Subform's Records
Mar 23, 2006Dear All,
Maybe my question is not too difficult.
I have a form-subform structure and my aim is to number certain records of the subform. These records would contain value: 1,2,...
And when changing the record on the main form, the subform's record should have the values 1,2,... again.
(These values are shown on the bottom of the subform, where we can step the subform's record.)
If I use an autonumber field, the numbering goes continously.
Please help me!
Thanks you in advance.
Sz.Cs.
Row (record) Numbering
May 28, 2005Hi there
I'm sure that this must be easy, so go gently on an old codger!
I have a query for which I wish to add another field containing a running total of the number of records produced by the query (after all sorting etc.).
In other words, if my query produces n records, that new field would contain the value 1 in the first record, 2 in the next and so on to n.
Any ideas would be much appreciated
Queries :: How To Reduce Number Of Item With Update
Nov 20, 2014How do you update a table by reducing a number by 10?
My assignment question is:
10 students have left GY101. Write an SQL UPDATE statement to reduce the class size by 10 for all modules taken by GY101 students.
I can display the students who take GY101 with the following code
SELECT moduleCode, classSize
FROM ROOM_BOOKING1
WHERE moduleCode IN (SELECT modCode
FROM STUDENT_REG1
WHERE sID IN (SELECT id
FROM STUDENT
WHERE courseCode = "GY101"));
But how to reduce the number by 10.
Reports :: Row (Line Item) Number On Report
Mar 26, 2014I want to have line item numbers on report look like this:
Number....Order Number
1............9533-1425-20
2............5866-3411-14
3............2332-2355-14
3............2332-2355-14 ->repeating item
4............4399-5208-12
5............7392-2305-62
5............7392-2305-62 ->repeating item
6............2332-2355-14
Each new row of record needs to have their own line item number starting from 1 with criteria if order number is the same then carryover line item number on that record. I know how to get line item starting from 1 to last by assigning unbound control field with entry "=1".
Queries :: Counting Number Of Days When Item Used For Last Week
May 27, 2014I need to count the days that an item of equipment is used for the last week?
The problem comes if an item is deployed within the week i.e not used for the full week?
I have two fields - MobDate and DeMobDate
I have to create a third called NOD (number of days) which will display the number of days in the previous week that the item was hired out.
Forms :: Way To Assign By Design Certain Item Number To Given Control?
May 12, 2015The code I am modifying looping through Me.controls collection to identify appropriate control types. Is there a way to influence the order (sequence) of that traversing? Or in different words, is there a way to assign by design certain item number to a given control?
View 4 Replies View RelatedGeneral :: Select Item Number From Drop Down Box To Create A Report
Jun 27, 2012I'm currently working on fixing an older 97 database that I've updated to 2010. I have just populated the Drop down box with about three fields. Ideally what I want to do is after having selected the item number from the drop down box I then hit the button that creates a report with the information about that item number.
At the moment...If the box is blank it reports all the item numbers, however if i fill the box(select an item) it returns nothing i.e the report is blank.
I've looked at the query that builds the report, there are only three fields that populate the. location, part number and description. from a tbl called MainDetails
the only other thing:
If([Forms]![frmReports]![FLoc] Is Null,[FUNCTIONAL LOCATION] Is Not Null,[FUNCTIONAL LOCATION] Like [Forms]![frmReports]![FLoc])
it has a criteria of <>False
*FLoc is the drop down box *functional location is the location field.
I believe all the above does is populate the report if Floc is empty.
What can I put to make the report generate what ever I pick in the drop down box 'FLoc' source the three fields from the 'MainDetails table'.
Reports :: Count What Is Sorted - Number Of Time Any Item Appears
Feb 28, 2014I have a report that i can order through buttons on report or openargs. Is it possible to get a field that would count the number of time an item appears i.e., if I sort it by employee, it would count how many times each employee is on the report, if i sort it by TypeOfMalfunction, it would count how many times each appears.
View 4 Replies View RelatedSumming A Subform Item
Apr 14, 2005I'm having trouble with a text box, which is trying to sum some date in a subform. All I get is #Error, yet I'm sure I'm referencing the item in the subform OK. I've attached the database as it's self explanatory (I stuck a label on it).
The form I'm having trouble with is frmPurchaseOrdersDataEntry and you'll see what I'm getting at from there.
Any help most appreciated.
Ste
Multiple Item Subform?
Nov 6, 2004I am creating a form that will require entry of first names, last names, and ID's of either none, or any number up to 15 people involved in a particular project. I am trying to accomplish this with a subform that links to the main form using the main forms primary key.
When done, you will be able to enter data into the record (project name, date, etc), go to the subform and enter the Primary Key (an AutoNumber), then the 0-15 names & ID's into the subform one netry at a time, with all entries linked via the primary key to that particular record. One problem is that the subform always displays the info for the previous records. I also want to be able to "get out" of the subform and back to the main form saving all subform data along with that record. (am I making sense?) and have the subform reset back to blank for the next record. I am completely lost and don't even know if I am approaching this in the right way. Any help would be greatly appreciated!!
General :: Date Picker - Deduct 1 From Total Number Of Item On Stock
Jul 30, 2012I'm creating s simple database to track my printer toner supplies, what i want is that..every time I select a date (using date picker) it will automatically deduct 1 from the total number of toners on stock. Right now, i have to input 1 in a field and it will update the remaining toners.
View 1 Replies View RelatedForms :: Highlight Unbound Subform Record That Matches Other Subform Record
Jan 22, 2014I have two unbound unlinked subforms residing on a 3rd unbound main form. When I enter the current record on Subform1 I would like the matching record(s) on Subform2 to be highlighted or otherwise formatted. I can get this to work for only the first record on subform2 due to the way I have my code setup on Subform1:
Code:
Private Sub Accounting_Unit_Enter()
'find where AUs match. only works for first Subform2 record
If Me.[Accounting Unit] = Forms!MainForm.[Subform2]!AccountingUnit Then
msgbox "Match"
End If
End Sub
My thought was that I needed to reference the Recordset of Subform2 and search for all AccountingUnits that match the current AccountingUnit of Subform1,
Forms :: Combo Box With Msgbox If Selected Item Is Not In Record
Oct 30, 2014I want a Messagebox to be appear if the selected item or if the input item in the combobox was not in the record.
I used this condition in combobox
="[asstnumbr] = " & "'" & [Screen].[ActiveControl] & "'"
but when I try this run code function in macro via IFF will become error
IIf([asstnumbr]<>"'" & [Screen].[ActiveControl] & "'",MsgBox("NO RECORD FOUND IN YOUR SEARCH"))
Pick Listbox Item To Open Specific Record?
May 19, 2012i 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.....
Setfocus On Repeating Item/accessing A Specific Item
Aug 18, 2006I have a form that simply lists the items:
DEPARTMENT_NBR and DEPARTMENT_NAME
In the table: DEPARTMENT_TBL
When I edit the field DEPARTMENT_NBR and it is in error (must be between 01 and 99) I want to put out a message in a MSGBOX and SetFocus back on the DEPARTMENT_NBR.
I coded the MSGBOX with vbokonly and then DEPARTMENT_NBR.SetFocus, but after the message displays and enter is hit for the ok the cursor jumps to the DEPARTMENT_NAME on the current line. Is there a way to get the SetFocus to work properly on repeating items like this? I can never seem to get them to perform the same as they do on non-repeating items.
Thanks for your help.
HERE'S THE EXACT CODE:
If IsNumeric(DEPARTMENT_NBR) = False Then
If DEPARTMENT_NBR <> "00" Then
MsgBox "DEPARTMENT NUMBER must between 01 and 99.", vbOKOnly
DEPARTMENT_NBR.SetFocus
GoTo DEPARTMENT_NBR_EXIT (exits the paragraph skipping other checks)
End If
End If
Also, is there a way to look at a specific item in a list like that? IE. I want to reference the 3rd row's DEPARTMENT_NBR. Thanks.
General :: Display Listbox Record When Double Click On Item
Sep 26, 2012I currently have a listbox. (list32) that is populated with results of a query. I would like to doubleclick on an item and have it open a form that displays the listbox record i clicked on and a "notes" field. Its my understanding that the table fields are limited to 255 chars so i was curious if I could make the notes field reside somewhere else. I will need the notes able to be edited, specific to record entry, and then saved on exit. Is this doable?
View 2 Replies View RelatedTables :: Procedure To Restrict / Stop Auto Number Increment For Certain Number Of Record Count
Mar 16, 2014I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
View 8 Replies View RelatedQueries :: Selecting Most Current Record For Item That Has Multiple Records In A Query
Oct 31, 2014I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
View 11 Replies View RelatedReports :: Creating A Report Based On Single Record In Multiple Item Form
Jan 8, 2014I'm currently working on a database which requires invoicing as a part of it. The invoicing is done based on quarters, and I want the users to be able to use a multiple items form, listing all of their clients, to create the invoices. Each invoice must be created individually so they can be e-mailed to the client, and saved to the clients folder. So I was wondering if it would be possible to create individual invoices for clients using a multiple items form.
View 1 Replies View RelatedGeneral :: Get Latest Record Added Of Item - Check Period Between Now And Date Saved
Sep 28, 2014In my Access app I need to get the latest record added of an item from a MS Sql table and check the period between now and the date saved in the record.
So my guess is:
Select TOP 1 * from tbl_StockItems
Where StockId = Loc_StockId
I then need to check the days between tbl_StockItems.LastStockDate and Today.
How would I write an Access VBA query to give me the amount of days and put that number into a variable.
Tables :: Change Item Number In 3 Tables
Jan 1, 2015We sometimes make an error in entering a stock code and have to change the code.
Our main table is = Investments01_tbl
Other tables that use the same stock codes from the above table are
= Alerts_Stocks
= Investments01_tbl_SubForm
The table = Investments01_tbl_SubForm = can contain the same stock code many times.How can we create a query that can be used to enable us to change the stock codes in the 3 tables at the same time?