Looking for some advise and help. I think an array will be recommended and if so, please help with code on how to do this.
I am trying to track prescription (Rx) dates (RxOutDate) going out to medical professionals on a particular order. There can be mulitple Rx going out so there will be multiple dates of when those Rx went out. What I would like to do is have the earlies date posted. ie one Rx went out on 10/13/04 and another goes out on 10/14/04. I would like for the 10/13/04 date to show. I'm using a continuous form to display all Rx's per order, on frmRx. I'm trying to display the date out on the form containing the order (frmOrder).
Any thoughts would be greatly appreciated. If an array is suggested. Please help with code on how to it. I've never written one so I would definitely be in the dark.
I am currently using some of the code from ghudson's browsing2.mdb for importing XLS files into a database that I making. After pressing the import button which triggers the importing of the file it imports the contents of the XLS into a table called import and into another table at the same time. Both the import table and the other table have the same structure. For the sake of argument lets call the other table "table B".
I want to know of a way of how during the import of the XLS it can flag with a message box to say that that the information already exists in table B, and exit out of it. I was thinking this would be done in VBA. I have written some part of it (very basic) and would appreciate someone's opinion if I am on the right track. It basically searches if the same data lies in the 2 tables and pops up a message box. I would welcome any other solutions, but I would prefer it to be VBA.
----Start of Code--------------------------------------------- Dim strCompare1, strCompare2, strAppend1 As String
strCompare1 = "Select table B.field A from table B where (select import.field A from import);"
strCompare1 = "select import.field A from import where (select table B.field A from table B);"
If strCompare1 <> strCompare2 Then strAppend1 = "INSERT INTO table B ( field A, field B, field C) SELECT Import.field A, Import.field B, Import.field C FROM Import;" CurrentDb.Execute strAppend1 Else MsgBox "Piece-Marks already exist in the database, so if it is an update then use the update function" End If
--------End of Code--------------------------------------------------
I have a wonderful database I designed almost 2 years ago. Other than a few hiccups with corrupted data (http://www.access-programmers.co.uk/forums/showthread.php?t=135483), my employer is 100% satisfied with it.
I’m graduating this winter with an AS in computer programming and I would like to update the database to a VB/ADO .NET (VS 2003) front end before I leave. Being a VB developer, I have had my fair share of “quick fixes” when I could not work out a solution with VBA. VBA is the evil twin of VB lol.
Other than PHP w/ MySQL I have never used two separate entities for data management. It’s always been strictly MS Access as the database and front end.
So my question is what major differences should I expect that I took for granted with MS Access’s built in functionality? Such as subforms with master/child properties, navigation, printing reports etc. Will I have to write code for every little detail that MS Access handled for me?
What are your thoughts on using VB.NET as a front end? Did the pros out weigh any cons from your experience with this setup? Any thoughts on using Crystal Reports?
I am trying to calculate the number of weekdays between two dates in an access query.
I'm using Expr 1: DateDiff("w", #4/01/2006#, #02/26/2007#)
When I use this expression with "d" instead of "w" I get 331 days, but when I use "w" I get 47 days. I know this can't be right.... Is there something I'm missing here?
I looked up on msdn and "w" is for weekday. Is there something that I need to do to be more specific?
Im planning a db client's dinner questionaire that will comprise of client's favorites (meals, games, transportation). Each client is allowed to bring a guest. So the client's will also be filling out "the same set" of questionaire for his/her guest.
Meaning, a user will need to fill out the same form twice, one for himself and one for his guest.
Now, my question is, should i have two tables. Client's questionaire table and Guest questionaire tables with foreign key in the guest's table showing who's guest the question belongs to?
Or should i have one table for everyone (since they are the same set of questions). Having one table seem like a wise idea since it's the same set of questions (don't want to have 2 duplicate tables with only one field different). Now, if i go with one table. How do i go about handling this? So that client's and guest's info are linked. and i can run a query to tell who's guest it is? I have never tried thsi before. Care to share your thoughts?
Is it possible to run a query when a check box is pressed? If it is possible how would i then go about comparing the result. I would like it so that a count query is run when the check box is pressed and then if the result is 20 or greater then a command button is disabled. Any help?
I have a crazy idea that I think would be very cool if I got it working, but have no idea where to start. I have a database that is broken down into a front end and a back end, and there are about 10 users who use it off and on throughout the day. This database has an entry form to enter new records. I am wanting to put a text box or a label on that main form that would turn green if the database was in use by another user, besides the one that was currently looking at the form. It would be red if there are no other users accessing the database. Is this even possible? Any suggestions or input would be greatly appreciated.
Hi . I need to have a single form , or nested subforms for data entry. The proble is the main form requires information from 2 other forms, and these forms in turn require information from other primary key fields. I know its basic form design, but i just cant seem to get my head around it. Any ideas?
tblVehicles VehicleID Autonumber PK MakeID FK to tblMakes ModelYear Number 'any other fields that describe ONLY the vehicle
tblMakes MakeID Autonumber PK MakeName Text
tblTires TireID Autonumber PK MakeID FK to tblMake TypeID FK to tblTypes DatePurchased
tblTypes TypeID Autonumber PK TypeName Text
tblTireLocation TireLocID Autonumber PK TireLocAbbr Text TireLocation Text (Front Passenger Side, Front Driver Side, etc.)
tblVehicleTires VehicleTireID Autonumber PK VehicleID FK to tblVehicles TireID FK to tblTires TireLocationID FK to tblTireLocations DateAdded Date/Time (Short Date) DateRemoved Date/Removed (Short Date)
tblVehicleChecks VehicleCheckID Autonumber PK VehicleID FK to tblVehicles CheckDate Date/Time (Short Date) Mileage Number
tblTireChecks TireCheckID Autonumber PK VehicleCheckID FK to tblVehicleChecks VehicleTireID FK to tblVehicleTires TireMM Number TirePressure Number
see below for the current code to find a duplicate surname (this works Fine)
now that ive properly thought it through what i need to do is insert a surename and if (by Chance) when i insert the Date of Birth if this is the same highlight it as a duplicate record. However if these people are twins then i need the option to insert the duplicate record.
any ideas on how to go about this, see code below.
Private Sub Text24_BeforeUpdate(Cancel As Integer)
Dim SID As String Dim stLinkCriteria As String Dim rsc As DAO.Recordset
'Check StudentDetails table for duplicate StudentNumber If DCount("Surename", "tblData", stLinkCriteria) > 0 Then 'Undo duplicate entry Me.Undo 'Message box warning of duplication MsgBox "Duplicate Name " _ & SID & " has already been entered." _ & vbCr & vbCr & "You will now been taken to the record.", vbInformation _ , "Duplicate Information" 'Go to record of original Student Number rsc.FindFirst stLinkCriteria Me.Bookmark = rsc.Bookmark End If
My primary key for my database a WorkOrder Number. The issue is that sometimes problems span a selection of WorkOrders. For example, if we get a lot of bad parts, 3 different WorkOrders might be affected, and each may have different resolution to the effect. To speed up data entry, I've had a request to make a button that copies the all the information from a current record into a new one.
If much of this information repeats, it this a poor way to handle this data? Currently it is pretty straight forward with ostly one->many relationships, and this would make things pretty complicated with a bunch of many<->many relationships.
I’m working to ever-improve a Help Desk-like database and I got this idea for “templates.” That is, a quick way to fill out the as much of the ticket form as possible when common issues come in.
So I’ve created a table (Templates)that mimics the structure of my ticket table (Incidents). I’ve gone into Templates and put the values I want automatically entered in each field and I’ve added a combo box and a button on my ticket screen. The combo box is populated, from Templates, with the name of each Template.
My problem is that I have no idea how to make each field on the Incident form populate with the values from the Template table.
I need help with table structure, They keep way too much info. Now that I have said that here is what I need help with. I have been asked to make a small "Ha ha" Database this is what small became. Most of the data for this is in a spreadsheet now I know I can import it where I want it. The main concerbn is that the main piece of information would be the Job Address. Because of all this other information I was not really sure how to setup the tables here is what I have started .
Buyers Information (tbl) Fname Lname Address City State Zip (with a look to qry)"cool zipcode code" Phones (3) Email Ect.
Buyers Realtor Info (tbl) Fname Lname Company Name Address City State Zip (with a look to qry)"cool zipcode code" Phones (3) Email Ect.
Sellers Information (tbl) Fname Lname Address City State Zip (with a look to qry)"cool zipcode code" Phones (3) Email Ect.
Buyers Realtor Info (tbl) Fname Lname Company Name Address City State Zip (with a look to qry)"cool zipcode code" Phones (3) Email Ect.
Job Information (tbl) Address City (Filled in by Zip) State (Filled in by Zip) County (Filled in by Zip) Zip (with a look to qry)"cool zipcode code" RoofType (With a lookup to A list tbl) RoofCondition (With a lookup to A list tbl) Job Notes (memo)
Appointment Information Time (now real sure how these time and date will work) Date ( " ) Appointment Type (With a lookup to A list tbl) Contact Name Salesman (With a lookup to Salesmen Table) Appointment Results (memo) Referral
As I mentioned this data is in a spreadsheet and most every row has all of the above information ("ha ha, small") but I wanted to get some other Ideas and see if anybody had a better one before I tryed to make it all work.
I may be oversimplifying my idea here, but I'm trying to have Form A, which has a source of Table A have fields automatically filled out with values from Table B when I select a template name and click a button that's on Form A. I simply have no idea how to make something this simple happen. Any ideas?
I've got 20 checkboxes which i need to do the same thing but individually.
If IsNull(DLookup("[question 1]", "qryQuestions")) Then chkQuestion1.Visible = False Else chkQuestion1.Visible = True End If
I tried this:
Dim loopy As Integer Dim tmpquest As String
For loopy = 1 To 20 tmpquest = "question " & loopy tmpCheck = "chkQuestion" & loopy If IsNull(DLookup(tmpquest, "qryQuestions")) Then tmpCheck.Visible = False Else tmpCheck.Visible = True End If Next loopy
tmpquest works. tmpcheck doesn't.
I tried dimming tmpcheck as lots of things but no luck.
I am thinking of adding a Request form to my database so that if the user wants to add a code to the system, they could fill out the form click send and i would then be able to retrieve all the requests via a report.
I have designed the form to something like what i am after...
i have a few issue's here:
A)
i have set up an option wizard so that user can select if this is a new code or a failure mode request
if the user selects "New Event Code" i want the the text box and label for Eventcode Description to become visible and all others to become inactive.
i will explain what happens if the user chooses "Failure Modes" in a minute.
B) I have no idea what is the best method to sort the following section, if you look at my form at the bottom (Highlighted in red) i need this to be a continuous option as the user may wish to add more than one Failure mode to an Event Code.
C) going back to my options if the user select "Failure Modes" i need the bottom section to become active.
D) If that was not bad enough i have no idea on how to store this in a table.
Definitions:
Event Code ---> Top Line Code
Failure Mode --> Is the Event code description but broken down further
I am looking to build and Overtime tracking form and have been pondering on the design of it. Here is my idea and not sure how I should go about doing it (straight forms and tables or queries). I would like to select my employees in a combobox and have their information autofill the remaining fields and/or add new ones. Additionally have fields that I can input any overtime occurred which would be stored in a seperate table but displayed in a sub-form in datasheet view. These fields that were just filled out with the overtime information would clear everytime the add record button is hit.
I know that sounds like alot and sure I can muddle my way through that part of it, the big question I have is do I need a seperate table for every employees overtime? Or is there code/query that can select the records of the selected employee and display the information that pertains just to them?
Has anyone come across or posess a sample database similar to this? If not can someone point me in the right direction for example code and tutorials?
Bax, I am still dealing with that problem of getting my statistics form to show. Is it possible for me to set it up so that when a new player is added into the player information table that the statistic table will populate itself with the [Uniform #], [FirstName] & [LastName] and default 0's to all statistical fields?
Hi! I have a client who wants a training tracking software built that will allow for simultaneous use by up to 850 users. Is Access a good idea, or should I use SQL, and if yes to SQL, can you point me to some great documentation supporting this? Thank you, thank you, thank you!
I would like to add a textbox to my main form so that users can enter in some extra data regarding the job they are working on. This text box should tie to the current record of the main database, tableJobLog.
Is it best to keep this textbox as a field in the main database, tableJobLog? Or should I create a separate table (perhaps tableJobNotes) with just this text field and link the two tables?
I would think that having it in the main table would make the table grow considerably in size after some time.
If creating a separate table is better, I would need some guidance on how to do this.
I put a split database (created in 2000 format using Access 2003 SP2) on the network three weeks ago. Three times the entries in two of the columns have been changed for every record, essentially making the data useless. I have not been able to narrow it down to a specific event that is causing it in order to program around it. Any thoughts would be greatly appreciated. Here are the details:
The table involved is called tblTitleLog. It contains 13 columns, the first of which is an autonumbered ID field. Column 7 is called CompletedCSA. Column 9 is called CompletedDate. The data in those cells could be different, from record to record, or could be the same as several records on either side of it. The data in those two cells can be updated either of two ways:
On a per-record basis using a form that contains controls tied directly to the cells for a given record, or On a form that incorporates a multiselect listbox and an Update button. This form is specifically designed to allow batch updating of those two columns.
On three separate occasions, the data in those two cells suddenly changed for all 6940+ records. For example:
This: CompletedCSA CompletedDate Timmy 2/2/2008 Bobby 2/5/2008 Joey 2/10/2008
Would suddenly change to: CompletedCSA CompletedDate Bobby 2/5/2008 Bobby 2/5/2008 Bobby 2/5/2008
No warning messages about multiple people trying to change the same record(s) is displayed. None of the users have been able to spot a trend in something they were doing when it occurred. The code for the Update button on the batch update form only changes the records with the ID of the SelectedItems.
This is the code from the Update click event on the BatchUpdate form:
For Each i In Me.listboxTitles.ItemsSelected strWhere = strWhere & Me.listboxTitles.ItemData(i) & ", " Next
If Len(strWhere) > 0 Then strWhere = "WHERE ID IN (" & Left$(strWhere, Len(strWhere) - 2) & ")" End If
Duplicating the same actions the users reported just before it happened didn't seem to make a difference. So far, I haven't been able to duplicate the conditions. Management is not happy and I'm frustrated. Any thoughts or suggestions on how to prevent this from happening again?
