Here is my code...if I take out the where statement..it copies the remarks all the way down my table to all the records...with the where statement..it's prompting in for parameters..
Private Sub Remarks1_Click()
Dim strMsg As String, strQry As String, strRemark As String
strMsg = "Update All Comments in the Current view. IMPORTANT!!! Will update a the Viewed records."
[Code].....
I have one memo per change (user input)...I need to have the comment the user inputs to copy into other records that were queried. I tried insert. My coding does one of the records or all of the records ..I need it to copy just to the queried records ...and I have a query but no filter set.
View 4 Replies View RelatedI have this access "Database" that contains only one table that I'm trying to make a form for. I want it to allow the user to enter a value for field A and a value for field B then a list of values for field C (which is the key, so each would be a new record all with the same values for field A and field B). I tried to make a multiple item form but when I click on the new row for field C, fields A and B both disappear.
Disclaimer: I know that the creation of a one table database is a mortal sin but that's what my boss gave me and there are already >8000 records with a million mispelled words, so I don't think there's much I can do to make it a relational database without entering each record in again.
I have a form with several subforms for entering information while surveying rooms in a building. I am trying to create a button to copy records from one of the subforms if the data is the same (for example if there are 3 types of flooring in multiple rooms) and append it into the same subform with a different space ID. I can not get it to recognize the Space ID on the current record. This is what I have in the command button code:
Private Sub AppendFloorCmd_Click()
Dim FloorTypes As String, SpaceUpdate As String
Dim CurrentSpace As TextBox
Set CurrentSpace = Me.SpaceID
FloorTypes = "INSERT INTO FlooringSurveyTable (FlooringHomoID)" & _
"SELECT FlooringSurveyTable.FlooringHomoID FROM FlooringSurveyTable " & _
"WHERE (((FlooringSurveyTable.SpaceID)=[Enter Space ID to copy]))"
[code]....
When I click the button, it appends the right records but makes me type in the current space ID - I've tried a bunch of ways of naming that control, but it will not work.
I have a subform for which I want to loop through all the records and then copy one of the fields for each row into another sub form.
I came up with the bellow code but i get an error when I run it.
The error is an "error 438".
Code is at follows and I am copying the field called price:
Do Until Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].EOF
[Roll Out - Sign items added].Form![Price] = [Roll Out - Sign items pick list].Form![Price]
Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].MoveNext
Loop
I've attached screenshots of the table relationships and some nested forms that I need to discuss in my database.
If you look at the forms screenshot you'll see I have a main form "business/cmc issues" that uses a combo box to select a business name; nested into that I have a second form "policy issues log" that holds details of policy issues about that business; then inside that I have a sub form "issue updates" that records brief details about the actions carried out in trying to resolve each policy issue.
The same policy issue can affect more than one business (because of a relationship between the two companies etc) but still needs to be viewed separately. So for example in the business selector combo box I might have business "ABC". In the policy issue it might say "doesn't pay on time". The "doesn't pay on time" issue might also apply to business "123" and so if I picked that business from the combo box you'd see the same policy issue.
Because it's the same issue for two separate businesses, the actions carried out will be the same, so what I want to do is, after a new action is carried out (where relevant) to be able to click a button that would run some code that copies the actions entered in the sub form for business ABC and pastes them into the sub form for business 123 where the product area and policy issue are equal. This is to avoid having to enter the same data twice.
Hi,
Would you humour me for a moment while I explain my query problem.
I work in Air Freight at Heathrow where all consignments are processed under an Airwaybill (AWB), an eleven digit number where the first three digits (prefix) denotes which airline the freight has travelled with. For security reasons a lot of the freight is X-Rayed and I am designing a database to log these shipments. Sometimes freight will arrive at Heathrow on one airline and then travel on to its' destination (this is called a transhipment) on another having first been X-Rayed.
What I want to do is produce end-of-month reports for each airline detailing which of their freight has been X-Rayed. I have created queries which filter the data for each airline according to the first three digits of the AWB, simple. On the data entry form I have a combo box which allows the operator to select which airline the freight has transhipped on. My problem is in the query where I have used the Or operator to "catch" these records e.g in the United Airlines report where the prefix is 016 I have all the records that begin with this prefix plus those that went on this airline but also those that went on a different airline but have the 016 prefix. It is these last records that I only want to appear once on the report of the airline it finished its' journey with.:o
Hi all,
I'm building a quick database, but some of the data to go into the database is quite... odd...
Anyway, the point is, I have one sheet that says
"Job Number 1200-1245" which would have all the same details, dates, etc.
Is there a quick and easy way to put in a range, say J1200-J1245, enter the data once, then be able to query... for example J1212 would return the same data.
Cheers :)
Hi,
I have a set of data and some are in capitals and some are not. This set of data is meant to be used for a mailing, and the home style of my company only allows the first letter to be a capital, the rest should be normal letters. Anyone have an idea how I can arrange this?
Thanks in advance!
Hi all,
I have a UNION query of multiple tables:
Select * From tblCal_SlsAB
UNION
Select * From tblCal_SlsBC;
Both of them have Unique IDs as PK, such as: AB001, AB002, etc for tblCal_SlsAB and BC001, BC002, etc for tblCal_SlsBC. When I do the UNION query I get: 1, 2, etc for both IDs instead. What's causing the ID to change and is there a workaround it? I need to get the same exact IDs I have in those individual tables. Thanks in advance!
Max
Hi all,
I am trying to design a database from scratch that duplicates some of the functionality in this database template from Microsoft: http://office.microsoft.com/en-us/templates/CT011366821033.aspx
In the form "Classes Subform" when you click on a Students Name the rest of the form is populated.
http://img215.imageshack.us/img215/2110/goodform0ib.th.png (http://img215.imageshack.us/my.php?image=goodform0ib.png)
But when I try to duplicate this form functionality in my Database I am not able to get the rest of the form to update.
http://img215.imageshack.us/img215/4845/badform9wg.th.png (http://img215.imageshack.us/my.php?image=badform9wg.png)
From what I can tell both forms use the same type of source query, relationship, etc.
Do you have an idea of what I may be missing?
Thanks Steve
Hi all,
I have a UNION query of multiple tables:
Select * From tblCal_SlsAB
UNION
Select * From tblCal_SlsBC;
Both of them have Unique IDs as PK, such as: AB001, AB002, etc for tblCal_SlsAB and BC001, BC002, etc for tblCal_SlsBC. When I do the UNION query I get: 1, 2, etc for both IDs instead. What's causing the ID to change and is there a workaround it? I need to get the same exact IDs I have in those individual tables. Thanks in advance!
Max
Hello there!
I would like to create a form where a clientnumber is entered in a textbox1, and when the value has been entered, the name, adress and other info about the client will appear...
I really need to get this one right.
Thank you in advance :-)
JR
I work in an office where we use the same database in a folder on a shared server. When Mary Jane is signed on to her computer she can't see all of the data for a record but I am able to see all of the data for the same record on my computer. So I had Mary Jane log out of windows on her computer and I logged onto window on her computer. Once I was logged onto windows on Mary Jane's computer, I then opened the database in the same folder on the shared server and I was able to see all of the data for that record.
View 1 Replies View RelatedI want to add a section to my report and control the visible value based on page (1st, 2nd, etc...)
So:
1st page show 'Page Header' which has lots of detail, but dont show 'AccNum.Header' section.
2nd, 3rd, etc.. don't how 'Page Header' as I don't need lots of detail, but show the 'AccNum.Header' as this has customer name and Invoice numer, which is enough header information of all the rest of the pages.
Tried using 'On Page' and logoc aroung [Page] but no progress.
I have a continuous form with an SQL query as its record source, it is a dynaset. Each row on the form is for a specific picture, containing a unique ID, a pre-generated caption field, and an official caption field. The idea is that users will edit the pre-generated caption field, hit a button, then their changes to the pregenerate caption will be made to the value of the official caption field.
My VBA code for the button works fine, in that it doesn't return any errors. The problem is that there is some sort of delay between when the button is hit and when the official caption field is updated. After the first press, the form requeries and the official caption field is the same, but after a second press the official caption field will display the user's changes.
First, a recordset clone is created using an SQL query, the pre-generated caption column is selected and then the results are filtered to the single record that shares the picture ID of whatever row the user was working in. Next, an SQL update runs, replacing the value of the official caption field with the value of the pre-generated caption that is contained in the recordset clone. Then the form is requeried.
I've tried adding a change of focus at the beginning of the code and a 15 second pause between when the update statement runs and when the form is requeried, neither solves the issue. The problem can't entirely be my code, because a second press of the button will make the appropriate changes.
I know what you're thinking, why have divide the captions into pre-generated and official in the first place - why not use pre-generated caption as the caption source? There is a separate feature that allows a user to mass edit captions and I decided to retain the original pre-generated caption as a field, in the event that the user makes a mistake or decides to reference the content of the pre-generated caption.
I have a report based on a query that returns all info from the query which is fine, I now need to amend this so that individual users can specify the date range to be queried and the person for whom the results are required (one of the query strings)...
View 1 Replies View RelatedHi all,Desperate for help on this.The query below ran like a charm for this years database which was full of entries, but when I did a quick test run for next year with limited entries the 'Temp' table fields were all blanked. All the individual queries, 1 through 9, work fine on their own and work fine in this linked form when there is enough data in the database to cover all the fields. Apparently if only one query is blank all the fields will be blank.For instance if there are multiple registered households, table ‘1’ would normally show a count of those households, but if none of those households have children, table '2' which counts children will be blank and in turn blank out all the rest of the fields including table ‘1’.I've tried the ‘is null, '=0" and ‘nz’ routines on the Count(Tablename.Fieldname)'s, but can't seem ‘to get them to output a value of 0 in the null fields. I must be doing something wrong. Any and all help would be very much appreciated.SELECT Year(Now()) AS ProjectYear, [1].CountOfClaimID AS Households, [2].CountOfPersonID AS Children, [3].CountOfPersonID AS Participants, [4].CountOfSponsorID AS SponsorsTotal, [5].CountOfHouseholdID AS Sponsored,[6].FamiliesWithChildren, [7].SponsoredFood, [8].SponsoredGifts, [9].SDSD INTO TempFROM (SELECT Count(Household.ClaimID) AS CountOfClaimID FROM Household GROUP BY Household.RegStatus HAVING (((Household.RegStatus)=-1))) AS 1, (SELECT Count(Members.PersonID) AS CountOfPersonID FROM Household INNER JOIN Members ON Household.HouseholdID = Members.HouseholdID GROUP BY Household.RegStatus, Members.Status HAVING (((Household.RegStatus)=-1) AND ((Members.Status) = "Child" ))) AS 2, (SELECT Count(Members.PersonID) AS CountOfPersonID FROM Household INNER JOIN Members ON Household.HouseholdID = Members.HouseholdID GROUP BY Household.RegStatus HAVING (((Household.RegStatus)=-1))) AS 3, (SELECT Count(Sponsors.SponsorID) AS CountOfSponsorID FROM Sponsors GROUP BY Sponsors.SponsorStatus HAVING (((Sponsors.SponsorStatus)=-1))) AS 4, (SELECT Count(Sponsorship.HouseholdID) AS CountOfHouseholdID FROM Sponsors INNER JOIN Sponsorship ON Sponsors.SponsorID = Sponsorship.SponsorID GROUP BY Sponsors.SponsorStatus, Sponsorship.SponsorshipYear HAVING (((Sponsors.SponsorStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())))) AS 5,(SELECT Count([Table0].HouseholdID) AS FamiliesWithChildrenFROM (SELECT Households_All.HouseholdID, IIf([Children]>0,1,2) AS FamChildrenFROM Households_AllGROUP BY Households_All.HouseholdID, Households_All.ChildrenHAVING (((IIf([Children]>0,1,2))=1))) as Table0) AS 6,(SELECT DISTINCT Count(Household.HouseholdID) AS SponsoredFoodFROM Household INNER JOIN Sponsorship ON Household.HouseholdID = Sponsorship.HouseholdIDGROUP BY Household.RegStatus, Sponsorship.SponsorshipYear, Sponsorship.FoodHAVING (((Household.RegStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())) AND ((Sponsorship.Food)=-1))) AS 7,(SELECT DISTINCT Count([Household].[HouseholdID]) AS SponsoredGiftsFROM (Household INNER JOIN Sponsorship ON [Household].[HouseholdID]=[Sponsorship].[HouseholdID]) INNER JOIN (SELECT Households_All.HouseholdID, IIf([Children]>0,1,2) AS FamChildrenFROM Households_AllGROUP BY Households_All.HouseholdID, Households_All.ChildrenHAVING (((IIf([Children]>0,1,2))=1))) as Table0 ON [Household].[HouseholdID]=[Table0].[HouseholdID]GROUP BY [Household].[RegStatus], [Sponsorship].[SponsorshipYear], [Sponsorship].[Gifts]HAVING (((Household.RegStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())) AND ((Sponsorship.Gifts)=-1))) as 8,(SELECT DISTINCT Count(Household.HouseholdID) AS SDSDFROM HouseholdGROUP BY Household.RegStatus, Household.SDSDHAVING (((Household.RegStatus)=-1) AND ((Household.SDSD)=-1))) as 9; Thanks much,aldo
View 2 Replies View RelatedOkay Stupid question, But I have tryed different things and have not got anything to work yet, So I am using Microsoft Acess 2003. I would like to be able to comment out block of code. Thanks
Sorry for the stundness...:confused: :confused:
I have a form, "Update Employee Information" that allows me to navigate through records and change data like name, adress, etc. I would like to know how to create list (or autofill) so that it won't take long before finding an employee.For example: I have a Employee ID and would like to search on that so the form can fill in the rest of my information. I have thought of two methods that I like:
(1) Click on a list "Lookup Employee", select ID and then the form will fill in the rest
(2) Without clicking a link, typing in the ID in the form and the rest of the data gets filled in.
Hi all
This gonna be a long one.... I've search the whole forum for answer to this problem, but couldn't find the one suitable enough.
I have a quote table with the following field:
QuoteName
QuoteDescription
QuotePrice
and booking Table:
BookingName
BookingDescription
BookingPrice
The real table is much more complex, but this will do for now.
When a client call up for a quote, their request will be added into the "QuoteTable". One name can occur many times depend on how many they ask for quote. Once they decided to proceed with the booking, all the records need to be transfered into "BookingTable".
Currently, I use either one of the following codes found in this forum:
Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset
Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
"WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable")
Do Until rstQuote.EOF
rstBooking.AddNew
For Each Field In rstQuote.Fields
rstBooking.Fields(Field.Name).Value = _
Nz(rstQuote.Fields(Field.Name).Value, "")
Next Field
rstQuote.MoveNext
rstBooking.Update
Loop
or
Dim lngOuterCounter, lngInnerCounter As Long
Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset
Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
"WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable")
If Not rstQuote.EOF And Not rstQuote.BOF Then
rstBooking.AddNew
For lngInnerCounter = 0 To rstQuote.Fields.Count - 1
rstBooking.Fields(lngInnerCounter) = _
Nz(rstQuote.Fields(lngInnerCounter), "")
Next lngInnerCounter
rstBooking.Update
End If
But none seem to work. Can anyone help?
Cheers
arnodys
Hi,
I wonder if it's possible in Access 2003 to comment a whole paragraph of VBA code with one click instead of commenting each line on its own?
Regards,
B
Hello,
Really simple one this one. Though I can't find it in the help or in forums. So simple it doesn't rate a mention apparently.
Except that I would dearly love to know how to comment out SQL code whilst writing queries in Access 2000 SQL view. All that retyping and copy/pasting is causing me RSI.
In eager anticipation,
Tim
I have a command button with the VBA code below. I'd like a message box to appear with a comment and an "OK" button that continues the code below . . is this possible?
Private Sub cmdDuplicateOverpayments_Click()
On Error GoTo Err_cmdDuplicateOverpayments_Click
Dim stDocName As String
stDocName = "qryFindduplicatesforOverpaymentsDetails"
DoCmd.OpenQuery stDocName, acNormal, acEdit
[Code] .....
Like in excel, when there's a red triangle in the corner of the field which pops up a small comment box, is there a similar functionality in Access that when a field.
Has either a comment associated with it, that a comment box can be made available?
I have a database and I wish to be able to copy 10 out of about 20 fields into a new record automatically using a command button on a form. Similar to the way the Duplicate button or the Create New button would work but only with these selected fields. None of the fields I wish to duplicate are the primary key. The primary key is an AutoNumber field. After clicking the Save button, the primary key would increment one to confirm the save record.
Any help or suggestion would be appreciated!
Thanks!