Assigning Data To A Query Using VBA
Jan 16, 2006
I have a form that has a button. This button is used to open a report. The command used to open the report refers to a query and sets the criteria so the report generated only returns values the button asks for.
I know how to set the criteria of a query using VBA, but is there a way to set a field that has no data to now have data?
I have a field in a query called Type and it is set to Null (SQL: Null As Type)
I would like to do this in the VBA, but subsitute the Null for a value that would be specific to the button.
View Replies
ADVERTISEMENT
Jun 9, 2014
I have a data base of ip addresses. I need to come up with a way to bulk assign Host names to the available ip addresses. The only data that is the same between the two tables is a Farm Name which is the same for each record. My first query creates a temp table that lists ip addresses by farm name that do not have a host name. That is my data pool. Then I import from Excel a list of host names (with farm name) that need ip addresses. What I am not sure how to do is "Combine" the tables. Putting a Host Name with an IP address.
View 1 Replies
View Related
Nov 12, 2007
Hi,
Complete newbie question, I have various yes/no boxes that i want to give a numeric value. The i want to add up these numbers and report it back to a text box.
Any help you can offer would be greatly appreciated.
Thanks in advance
Martin
View 9 Replies
View Related
Feb 15, 2005
I think this should be a simple question, but I just cannot figure it out. I have a form which contains a check box. I want to set the value of a field in a table to 0 if the box is unchecked and 1 if the box is checked.
How can I do this
:confused:
Thanks
R Tomalin
View 12 Replies
View Related
Sep 14, 2006
Hi all!
In my very first thread i bring a doubt that persutes me since laste Friday. I have search in the web an in this forum but, unfurtunantly, I didn´t find a answer for my problem. I hope some of you can help me :)
I am currently trying to construct a Data Base which will keep between 1.000.000 and 5.000.000 registries, and will have about 80 to 90 Fields. As you can see, its very much information to be hold and i need to ensure that the queries, that will be made to this DB, will be the simpliest and fastest as it can be possible.
Anyway, my problem is the folowing one:
I have a table, named Person, and in that tabe I got a field named Age and other on named Desig_AgeGroup. I want to assign the Age to the correnpondent AgeGroup automaticly (e.g.: if there is a registry in the field Age of the table Person thas is equal to 12, I would like that in the Field AgeGroup, in the table Person, appear " Between 1 and 14 years old" after that i typed 12 in the Age field, or after I imported Data to the field Age). For this proupose I had created another table, named AgeGroup, whit the following fields: Cod_AgeGroup, Desig_AgeGroup, Age_min, Age_max. At this point, my idea is to compare the Age, from the table Person, whith Age_min and Age_max, from the table AgeGroup.
However, the only thing i have acomplished whit this method was a Text box that apear in the field AgeGroup whith all the Age groups that I had specified in the field Desig_AgeGroup of the table AgeGroup.:(
Can someone tell me How can the Age groups apear automaticly in that field?
I hope I have been explicit...and sorry my poor, poor english
I aprreciate, since now, any help i can get from you.
Cumps
View 14 Replies
View Related
Feb 12, 2007
Yo there - thought Id fire a question out regardin numbering:
I have a table players and a table team.
Players sign up to teams, and I have a report which shows each team, and the players underneath.
Team
Player 1
Player 2
Now, after all the players are assigned to a team, I want to assign each player within each team a random number, sequential.
So when I run the report, it will be like:
Team A
1. Player 1
2. Player 2
Team B
1. Player 1
2. Player 2
Any pointers!?
Cheeas -
View 9 Replies
View Related
Jul 9, 2007
I notice that this topic has been done to death.....I have a database that is embedding the pictures and the predictable problems has manifested...the database is swelling up....
I'm learning to adapt the database to include the file location in the form and have the picture box refer to the file location, rather then embedding the picture into it....the problem that I have is that although I know how to do it because I get it.....this database will be run by a number of people, many of which don't have much experience and it must be simple to input the file location. Is there a way to simplify putting the file location in the text box aside from typing it in manually (a search window; like the one that appears when trying to open a file or insert an object, perhaps)....
View 1 Replies
View Related
Jul 26, 2005
Hi, I've lost touch of Access few years back & now I'm back again using Access... kinda rusty & yet rushing for a proj... so I'll very much appreciate if someone can help me on this..... :)
I've a database storing inventory details & after adding these details, i'm supposed to have another form where i'll start assigning these assets to different employees..& of cos, i'll need to edit this form over time if the employee left the company or asset is being transferred to another person. However, this asset list is quite comprehensive with thousands of records, so it isn't feasible for me to list all assets at one go & start assigning different asset to different employee... any idea how I shld go abt assigning? shld i minimise the search by invoice key & track no? (each invoice contains a few grp of items & each grp will contain several items, therefore, i've assigned an invoice key for each invoice & track no for each item.)
any help is very much appreciated.... thanks :)
View 1 Replies
View Related
Aug 22, 2006
Hey to all. I have this form in which I need according to a user selection to assign a picture and display it on the same form. Is this possible? How do I do this? Or can I display the picture to another form or a report based on the first form?
Thanks in advance.
View 2 Replies
View Related
Mar 1, 2005
I have a database that will be ran over a network. I want the freedom to add fields to the table if needed later, but I don't want anyone else to have the same permissions as me. I made an attempt at using the wizard to assign permissions, but it didn't turn out the way I needed it to. Can someone help me with this please?
View 1 Replies
View Related
Aug 30, 2006
Hi
I am assigning a value from a table to text box on the form through VBA. It gives the following error :
Run time error 2115
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing MS Access from saving the data in the field. I have no BeforeUpdate or Validation event for the text box. What else could be the cause ?
The text box is unbound and I use ADODB connection.
Please assist.
View 5 Replies
View Related
May 28, 2013
I've built a query that selects certain customers based on a purchase date. I now need to assign these customers to various sales reps and display the customer information to them in another form that they can alter information in.
I was going to put my sales reps id into an array and loop it through the query updating it. Let's say i have 7 sales reps working that day, I would then enter their id's into fields and have the array assign these numbers into the query Sales Rep Id field for each row.
View 6 Replies
View Related
Oct 29, 2012
We're developing a database to manage tournament registrants as well as event results.
The key tables we have ar:
ContactsRegistrationsRegistration DetailsTournamentsAgeGroupsBreakingAgeGroupsExperienceGroupsEventsPayments
There are queries for using information in the tables to determine age, competition divisions etc.
At the moment the RegistrationExtended query, which uses the ContactsExtended and TournamentsExtended queries and the Registrations table to determine the number of events for each registrant as well as what division and age group they are assigned. This is done with some IIF expressions.
For the 2012 event we had 4 division classes based on experience and 6 age groups. For 2013 there might be more division classes and age groups. So now I have the tables AgeGroups, BreakingAgeGroups, and ExperienceGroups. The two age group tables have a query that calculates the age group name based on the values in the MinAge and MaxAge fields. For example 8 & Under, 9-11, 12-14 etc. The ExperienceGroups also have a minimum and a maximum field but only one field will be completed for each experience level. If the value is a maximum then that would be for those with less than the maximum experience and if the value was a minimum then that would be for those with minimum of that many years of experience. For example Grasshopper for less than 1 yearWarrior for those with 1 year to less than 2.5 yearsSamurai for those with 2.5 years and less than 4 yearsShogun for those with 4 or more years.
We want to maintain the history so the age groups and experience groups are now tied to the tournament.
I'm think that I'll need some VBA code that will compare the registrant's tournament age to the ranges assigned to that tournament and will assign them to the correct age groups. Then there will be code to compare the registrant's experience and assign them to the correct division class. The key here is that each year might have a different number of age groups and experience groups as well as different experience group names. For instance in 2013 we may change the 8& Under group to 7-8 and add a 6 & Under group and we may add another level called Emperor for those with 20 years or more experience.
View 12 Replies
View Related
Jul 26, 2005
Hi,
I am trying to setup User Level Security around a Purchase Order Database that I created. Using the Wizard I was able to setup different users with different permissions.
However, as I found out the hard way (but I did recover it), 'joining' a PC to a my new security workgroup forces all Access databases on the PC to use this.
Is there a way to force just this one Access .mdb file to use the newly created PO Workgroup security file ?
I am aware of setting up a shortcut to point to thesecured db. Ie: "C:Program FilesMicrosoft OfficeOfficemsaccess.exe" /wrkgrp "C:PO FormsMyWorkgroupFile.mdw" "C:PO FormsMyDB.mdb" /user
But despite the above shortcut, couldn't users just find out where it points to (by looking at the Properties of the shortcut), and open the .mdb file based on that ?
Thanks !
View 1 Replies
View Related
Mar 27, 2006
I am working on a small database for a school to track their live and silent auction. Currently I have a table set up as follows.
Silent Auction table :
Item Number
Short Item Description
Long Item Description
there are more fields that I am not interested in right now.
Mainly what I am after right now is to do some decent reporting for generating the catalog and posting the silent auction items on the web site for viewing before the night of the event.
I need to generate a report that contains the Item Number, short and long description. I need to group on Category which corresponds to the item numbers. Example:
Item Number 100 - 110 = Class Projects
110 -199 = Live Auction Items
200 - 299 = Sports and Recreation
300 - 349 = Entertainment
and so on......
I realize that had they numbered them in the 100 range I could group in my report by 100's and everything would be pretty good however, I did not have the opportuinity to be involved in the numbering scheme.
Even if the numbers had been in the order of 100s I still would need a way to say that the 100 -110 range is Class Projects.
I think I need another table and link it but I am not sure how to set up the range of numbers for the category.
Sorry to be so wordy.
Thanks in advance for the help.
Lee
Network admin trying to learn Access.
View 3 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
May 8, 2006
Hi there, I am pretty much a newbie on Access. (yay). Now I am doing a simple contacts database and need to assign whether specific contacts wand information being sent to them. Now I have all the other fields required. But the check boxes that I have put on the form are not changing according to the contact. (as in if the check box is ticked, it is ticked for all of them):( :confused:
How do I go about assiging it specifically to that contact? - If I am making sense. Any hints/tips would be greatly appreciated:)
View 1 Replies
View Related
Dec 2, 2013
Database has a form to add a new item. This info is stored in the Item TBL. There is also a table that lists available UPC codes called UPC TBL. When a user enters a new item a dialog box will give them the choice to add a UPC code for that item. Clicking no - nothing changes and user return to the original form (no brainer) But if they click yes what I need to happen is:
1. The next available UPC code in the UPC TBL is entered into the Item TBL field for UPC.
2. The UPC TBL is updated to remove the assigned UPC, leaving it ready for the next assignment.
3. - and for another day - I need to create a message when the UPC table is empty because all UPC's have been assigned.
View 7 Replies
View Related
Aug 15, 2013
I am working on an app, that has a field named "Name". I have everything set up with a module, which, for example, the name "John", it assigns 1. when it sees "John" again it assigns 2. Then "Jane" It assigns 1 again. I want the module to see every instance of "John" to assign the same number, 1, then all instances of "Jane" 2, etc. Here is the code in the module I am using, but it is assigning the values wrong. My final plan is to use the numbers for conditional formatting, so all johns one color, all janes, a different color. I can't use the conditional formatting wizard because these names pop up at random, and the names populate at random.
Option Compare Database
Global GBL_Category As String
Global GBL_Icount As Long
Public Function Increment(ivalue As String) As Long
If Nz(GBL_Category, "zzzzzzzz") = ivalue Then
GBL_Icount = GBL_Icount + 1
' MsgBox icount
Else
GBL_Category = ivalue
GBL_Icount = 1
End If
Increment = GBL_Icount
End Function
View 1 Replies
View Related
Dec 16, 2014
I have two tables. The first is a list of users. The second is a list of projects. I need to loop through the user list and assign each one to a project. At the end of the user list it will just start over at the top until all the projects have someone assigned to it. What is the best way to do this?
Table1 Users
User1
User2
User3
Assignments
ProjectAssigned To
Project1
Project2
Project3
Project4
Project5
Project6
After processing.
ProjectAssigned To
Project1User1
Project2User2
Project3User3
Project4User1
Project5User2
Project6User3
View 8 Replies
View Related
Sep 11, 2014
How can I assign some type of index to the below variables (data representation) ? I'm using VBA to build a .RFT file for MS Word.
"Line # 1 data"
"Line #2 data"
"Line #3 data"
. . . .
"Line #7 data"
"Line #8 data"
"Line #9"
. . . .
"Line # 22 data"
"Line #23 data"
"Line #24 data"
View 14 Replies
View Related
Mar 31, 2013
I have an access form which includes my primary key field "ID". Normally when I use this form to enter a new record this field displays "(New)" until I enter data in any other field, then the ID field updates to reflect the new value. This worked fine when the table I was adding the record to was a table linked from another access database.
I recently moved my data to SQL Server 2008, without making any other changes to the application, and this behavior has changed. Now when I enter data into a new record the ID field changes to "NULL". And I am not able to reference this value in my program (Me.ID) like I used to be able to. When I save the record the ID field gets assigned and everything works as normal.
So I am able to make my application work again by forcing the record to save before trying to reference Me.ID, but why the behavior changed when my table moved from Access to SQL Server.
View 1 Replies
View Related
Oct 9, 2014
Three tables:
Inventory
InventoryPK
ShowLink
ShowFK
InventoryFK
Shows
ShowPK
There's an inventory of products that I might take to a show (convention). What's a good way to associate the show with the product, and store that relationship in the ShowLink table?
I could make a datasheet with one of the fields a combo box, but this would get tedious if I had 100 products. I figure a better way would be to have a multiselect list box that I select all the products, then have one combo box to associate a show to it.
View 14 Replies
View Related
Mar 8, 2012
There is currently a database which contains location information on a variety of different samples, each which is identified by a unique sample number (the primary key). However, when these samples are processed, they are split it half and each is assigned a second identifier for each half (in this case, an A or a B). Therefore, if originally there was sample 1, it will be split into 1A and 1B. My task is essentially to create a second database with further information for each sample half (1A or 1B), while maintaining a link to the main sample database to reflect any changes to the sample location information. So far I have been using a make table query that pulls from the linked table which links back to the main database and combines with another table that simply has one column called "Piece" which has an 'A' in the first row and a 'B' in the second row.
I am quite new to using access, but I have found this task fairly frustrating so far because it feels like I am trying to force access to work in ways that it was not designed. Essentially I would like access to create a duplicate entry for each sample in the original database, and force in an 'A' or a 'B', and combine these fields to create a unique identifier. However, I am running into a lot of issue when trying to append data from the original database etc.
View 6 Replies
View Related
Sep 30, 2011
I'm creating a database of companies. Every company has at least one field of services they provide. There are three main groups which contain all the services (Let's say I have three tables).
What I need is to have a form/table (query?) where I can assign to a company any number of services from any group. Is it possible to do it in a form where I could simply add a service by choosing a group first and then a proper service, and then add more in the same way?
View 3 Replies
View Related
Nov 15, 2013
I'm trying to export a report as PDF giving it a name at the same time. I'll then email this out.
The email bit I'm fine with, but I'm struggling to get the report to save as a PDF
Dim outputFileName As String
outputFileName = CurrentProject.Path & "Mail_MergesTemplatesBulkEmail Quote From Dialler" & "Quote" & [Quote_URN] & ".csv"
DoCmd.OutputTo acOutputReport, "Digital Service Cover Quote", acFormatPDF, outputFileName
View 3 Replies
View Related
