Modules & VBA :: List Of Orders - How To Keep New Types In Same Table
Oct 26, 2013
I have a query which is my list of orders. Now I want to create other type of orders. New types don't use the code in red (tbl Offset and tblRodzajZlecenia). Is it possible to modify the query, in order to keep new types too?
New types of orders are in the same tables. But if I don't fill fields in tblOffset then query can't show new type of oder - that's the problem.
Code:
SELECT z.ID_Zlecenia AS ID, tblRodzajPracy.RodzajPracy AS [Rodzaj pracy], tblRodzajZlecenia.RodzajZlecenia AS [Rodzaj zlecenia], z.NazwaZlecenia AS [Nazwa Zlecenia], k.NazwaSkrocona AS Klient, z.DataPrzyjecia AS [Data Przyjęcia], z.TerminOdbioru AS Termin, tblStatus.Status, tblStatus.lp
FROM (tblKlienci AS k INNER JOIN ((tblStatus INNER JOIN tblZlecenia AS z ON tblStatus.Id_Status = z.Status)
[Code] .....
View Replies
ADVERTISEMENT
Mar 5, 2014
I have a database where I have 3 tables. first is client data, second orders data and third the products data.
What I would like, is to have a button on my form that after selecting from a combobox a product, it wil enter it in the tabel orders in the next available free record (row). I created a button and the combobox in the client form. The Products table has also the price per unit in it. It should take the whole row of data and put it in the Orders table.
Is there a way to do this? I guess it needs to be done with vba, but not sure.
View 9 Replies
View Related
May 7, 2005
Dear All,
Can anyone help me with a problem I have with this database.
I want to be able to assign members (Contacts) of companies (Company) to training courses.
I just do not know how to do it. Contacts will need to be able to be assigned to many courses and many different contacts can go on the same course. I think it can be treated as picking inventory items from a list to make up an order, but I cannot get my head around it.
What I want to be able to do is to go through the courses button, double click on the course ID field to get to the course information screen. (I have already set this up) I want to be able to choose a course and select a member of the company to go on the course by using a yes/no box.
I have set up a course attendance table but do I need a query or something to link contacts of the company to the courses table. Is there a problem with my relationships?
zip file attached.
As someone new to access, this is hopefully easy, I just don’t have the know how. Please any help would be excellent and id be very grateful.
Thanks. Please help.
View 1 Replies
View Related
Jul 26, 2013
Dim MooringLines As String
Dim MooringLinesPrice As Double
MooringLines = DLookup("TotalComponent", "ComponentT", "[TotalComponent] = '" & Me.P_MooringLinesCmb & "'")
MooringLinesPrice = DLookup("EuroPerMetre", "ComponentT", "[TotalComponent] = '" & Me.P_MooringLinesCmb & "'")
Me.P_MooringLinesTxt = (MooringLines + " - " + (MooringLinesPrice * Me.P_LengthMLTxt.Value))
In the first variable I'm trying to get a string and combine it with a double in the second. Is it that I can't mix a string and a double in the one variable and if so how do I convert the double to a string?
View 4 Replies
View Related
Jul 25, 2014
For reasons I need not get into as it generally proves to be a distraction, I need to document the relationships in databases.
I've been playing around with .Relations with some success.
I can easily get the relationship's name, Table Name, Table Field, and ForeignTable.
Code:
For i = 0 To objDestDB.Relations.Count - 1
With rsMigrateRels
.AddNew
!RelNumber = i
!RelAttributes = objDestDB.Relations(i).Attributes
!RelFields = objDestDB.Relations(i).Fields.Count
!RelFieldsName = objDestDB.Relations(i).Fields(0).Name
!RelForeignTable = objDestDB.Relations(i).ForeignTable
!RelName = objDestDB.Relations(i).Name
!RelPartialReplica = objDestDB.Relations(i).PartialReplica
!RelProperties = objDestDB.Relations(i).Properties.Count
!RelTable = objDestDB.Relations(i).Table
.Update
End With
Next i
However, despite lots of digging into the other properties and attributes associated here, I cannot yet figure out how or where to get the:
ForeignTable field
The type of relationship (1:1,1:many,many:many)
View 14 Replies
View Related
Apr 20, 2005
Hi,
I am designing a database.
It is going to store data from questionnaires.
The natural way seems to be to have a table - that stores where the data comes from, what is it about, what is the question and what is the answer, like this:
DataProviderID; FocusID; QuestionID; AnswerValue
My problem is that a Focus could be a eg. a Person, a Company or a Job - meaning that somebody may be giving info on an other person, on a Company or on a Job.
Now Persons, Companies and Jobs would have very different properties.
So how should I solve this shold I have a common table for Peoples, Companies and Jobs and use a very complicated structure of properties for them or should I store these entities in separate tables but than how will I store the results?
Thanks for your thoughts.
SWK
View 3 Replies
View Related
Apr 19, 2013
I am now working on this new database. I would like to make a calculation so that I could derive a value called eGFR from a patient's serum creatinine. This is a test of kidney function.I am having the following problems.
a. Modules are not appearing in the Navigation Pane of the Access 2007. When I press 'Object Types' in the Navigation Pane I get all object types such as tables, queries, forms, reports, macros but not modules. I know there are modules as I have written lots of vba code.
b. I have written the following code within this Function. The following is the code.
Public Function eGFR(creatinine, Gender, Age) As Single
If IsNull(Age) Or IsNull(creatinine) Then
eGFR = 0
Else
[code]....
This code is working fine in another database, however when I try to upload a query in the current database that has this function in one of the fields, I get an error saying the the function eGFR does not exit.
I have inserted the function into one of the modules that I have managed to enter.
Do I need to deposit the code elsewhere? How do I access Modules from the Navigation Pane?
View 2 Replies
View Related
Feb 26, 2014
I have a database with 2 tables: tbl_Client and tbl_Orders
I would like to sum the total from the sold products and show them in the form in a text field called 'Total sales" the data from the table orders appear as a sub form in the main form Client.
It would be great to have for each client appear the total sales in the form, but I'm not able to find the formula for this so the table tbl_Orders field "TotalPrice" will be sumed.
View 14 Replies
View Related
Aug 9, 2011
I am trying to create a query based on a transaction table which compares the time between two types of transactions.
The initial record would have "A' in the type field and the second record would have "D" in the type field.
each pair of A and D records would be matched on o_time and idnum fields, and I would want to pull each pair and us the datediff function on c_time, using records marked with A as the initial time and records marked with D as the followup time.
Is there any way to accomplish this in a single query or do I need to create two subqueries?
View 1 Replies
View Related
Oct 3, 2012
how do i get form to generate a new order ID for the same customer in my orders table
View 2 Replies
View Related
Feb 4, 2014
I have a Client database table in Access. I now need to add a simple order table (related) to the client table. I have a client ID field set to autonumber in the client table. As I start to create the orders table I'm not sure how to link the two so that I'm not entering data twice and have assurance that they are tied together.
View 4 Replies
View Related
Apr 23, 2007
Hi Everyone,
I have a PO table. Each PO has 3 documents of multiple types (one may be Word, other may be Excel, Acrobat). When I display the PO, the attachments should be shown as Icons, which when double-clicked, will open in its own Application.
I have a pop-up screen where I enter new PO details. In this screen, I have Attach/Detach buttons for each of the Documents.
My Requirement:
When I press 'ATTACH' of Doc1, the File Selection screen should be displayed from where the user will select the file to be attached. This file should be updated into the table field "doc1".
When I press 'DETACH' of Doc1, the file in table field "doc1" must be cleared.
How can I achieve this???:mad:
View 1 Replies
View Related
Jun 5, 2014
I have a query that selects invdate, status and type from a table, I want to be able to search for invdate=today status between 50 and 70 and to count types 30, 31+32, 33+34 and 35.
View 4 Replies
View Related
Jan 23, 2013
In my database I have a table of customers and a table of orders (where many customers have more than one order, so the primary keys for each table are customerID and orderID).
In the orders table, the first columns are:
OrderID CustomerID Customer Forename Customer Surname
I need my table to auto fill in the customer forename and surname based on the selection of customerID (the names are saved in the customers table).
I already have a lot of data filled in, so am looking ideally for a way to fill these name columns without having to re-input all the data.
View 2 Replies
View Related
Jan 31, 2008
This the first time I post in this forum and I tried looking for an answer to my question before posting, so I apologize if my question has been answered previously.
Is it possible to determine, in Access 2000, the data type in a Make-Table query of a new column with null value so that it does not default to binary.
Here is my sample sql:
SELECT tblAddressBook.Name, Null AS Email INTO tblTestTable
FROM tblAddressBook;
The output is tblTestTable with two columns: Name and Email. The data type of column Email defaults to Binary. How do I make it default to Text.
Thank you.
View 4 Replies
View Related
Jul 10, 2014
I am trying to populate a ListBox with the names of the tables from another database. I have the following function which loops the table names from the database I want. The function is caleld on Form Load and passed into the ListBox but the List Box is empty.The message box in the function does display the table names when called from the form. I just cannot get the list box to populate.
Code:
Public Function ListTables()
Dim db As Database
Dim i As Integer
Dim s As String
Dim tdefs As TableDefs, tdef As TableDef
Set db = DBEngine.OpenDatabase("C:MyPathAnalyzed Tables.mdb")
[code]...
View 5 Replies
View Related
Jul 11, 2014
Save Item in listbox (Row source type : Value list) to a table in a database .
View 3 Replies
View Related
Mar 19, 2014
Im trying to delete values from a table based on the selected values of a listbox. The listbox values have a hidden column which related to the ID on the table to which I am trying to delete from (if that makes sense).
If I use the following code:
Code:
Set ctl = Me.Results_listbox
For Each varItem In ctl.ItemSelected
CurrentDb.Execute "Delete * FROM Table1 WHERE Table1.ID = " & ctl.ItemData(varItem)
Next varItem
I get an error stating Object doesn't support this property or method.
View 7 Replies
View Related
Nov 5, 2013
I a trying to list all fields from ALL tables in a certain DB.I am using the tableinfo function. However, because the output of the immediate window is limited to 200 lines, I can only see the last 200 fields. Is there a way to export this in another way than the debug.print procedure, so I can bypass this limitation?
Code:
Public Sub showtablefields()
Dim db As Database
Dim tdl As TableDef
[code]...
View 5 Replies
View Related
Nov 21, 2013
Upon the results which populate my list box what I am trying to do is allow the user to select various values from the list box and then hit a button which deletes the values from the table. My code is currently:
Code:
Private Sub Del_TsCorrection_tbox_Click()
Dim lngID As Long
Dim strSQL As String
[code]...
But it doesn't delete, infact it doesn't do anything. The list box values are still in the list box and if I look in the table they are still there also.
View 10 Replies
View Related
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 13 Replies
View Related
Aug 2, 2013
I have a process that reads a list of files sequentially and then processes them .... As each file is processed, its name (strfile) gets written to a Table (Vault.[Processed_file])
I want to verify if the file exists in the table prior to processing it - if it exists, I won't process it again ...
View 5 Replies
View Related
Nov 24, 2014
I have database with an userform called AssignWP, combobox called WPDevBy, listbox called List352 (Multi select) and table called Justified.I am trying to update one field WPDevelopedBy of the table as combobox value based on list box multi selected records.
View 2 Replies
View Related
Oct 4, 2013
I recently upgraded a DB from 2003 to 2013 and ran into the following problem.
I have a button that opens a file dialog box and allows the user to upload a file to a predetermined location (and store the address as a hyperlink). I borrowed this code from someone else on here and modified it slightly.
In any case, the button still works, but now when it opens it doesn't have an option for "All files" under file types. So I can upload MS Office files, text files, etc., but not PDF files which are by far the most common types my users upload.
Here's my code and a screenshot is attached.
Private Sub Command35_Click()
Dim dd As Integer
Dim fileDump As FileDialog
Set fileDump = Application.FileDialog(msoFileDialogOpen)
dd = fileDump.Show
[Code] ....
View 3 Replies
View Related
Dec 14, 2006
Hi All a newbie here so any help will be appreciated,
sorry for the long post but trying to give you all the information you might need.
I wrote a basic access database for my Church to aid in a paperwork audit for a charity food drop which we do monthly to give free food to the needy.
But each month it gets harder to find out who was in line first so I thought with all your help we may be able to randomize the names each month in a different order as to avoid confusion and also avoid people waiting in line as they turn up at 5am and we don't start until 9am.
So if this will work in access they can all come for 9am
I don't mind creating a new database and adding the additional information, if that's what it would take.
My Background I have created basic databases from scratch not using wizards, But I don't know much about code or how to implement it so any help in where code goes it would be very much appreciated.
Database details (Microsoft Access 2002 version)
Table Name = details
Field name = ID (auto-generated)
Field name = FirstName (text)
Field name = Surname (text)
If possible it would be nice to keep a record of the randomized lists (in the database somewhere ?) each month in case anyone wants to see it or disputes the lists, where I can just create a report to show the details.
There will be approximately 90 to 125 names.
Thank you in advance for all your help in this matter
Britgent
View 1 Replies
View Related
Aug 21, 2013
I created a form and created on it a list box which is a query that grabs certain number of fields from different tables. I would like the user to select from this list box of a choice and then store their selection into a table.This list box has three fields, but it needs to store the id rather than the item, the user would see the name of the item but the id of the item would be store into another table, called bid. It store all these three fields when a user selection one of the item from the list.
View 13 Replies
View Related
