Partially Fill Key Fields
Oct 29, 2006
I have a mainform Transactions with TransactionsID and a subform Site Details with SiteID. For non-database related issues (my users like things the way they are) at the moment I have to use text strings for the key fields instead of autonumbers.
SiteId begins with the TransactionID number (the program does check for uniqueness for TransactionID). At the moment the user copies and pastes the TransactionID into the SiteID textbox, and then just needs to add the site name.
I would like the SiteID to have the TransactionID partly filled in, so that one more step is eliminated. I have tried using forms!fTransactions!TransactionID, but I get #Error. I have also tried copying the TransactionsId text box onto the Site Details form and setting the property to invisible, and then using the forms! and I still get #Error. My first attempt was to change the data control of SiteID to TransactionID, but that didn't work either - it changed all the SiteIDs to their respective TransactionIDs. Fortunately I was working with a copy (rule number one of database design) and no harm done.
Is the issue that I am dealing with is that SiteID is a key field and that key fields can't be calculated? If so I will long for the day when all my key fields can be switched to autonumbers.
View Replies
ADVERTISEMENT
Jan 29, 2014
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code:
SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
That is the query.
View 14 Replies
View Related
Nov 2, 2005
How can I get some mainform fields' data to be the first entry in a subform? From the mainform, I would like the Head of Household name and date of birth to be carried over to the first entry in the HouseholdMembers subform.
The two forms are tied to separate tables. For each household member I need to be able to enter full legal name, date of birth and some additional pieces of information such as income and source of income.
What I'd like to avoid is making the users enter the Head of Household's name and date of birth twice. Is there a way to carry that information over to the subform?
Thank you, Charlotte
View 14 Replies
View Related
Feb 19, 2008
Hey guys, I have the below code that exports data to excel. Works wonderful, except when the Excel file opens, only the top portion of excel is visable. I would then have to save the excel file, then close both excel and access. Then once I reopen the excel file it is now visible. I would like the file to open over all other application including my Access application. Is there any way to force the excel file to open fully? Is there something I am missing. It seems to work the first time, but if I re-run the export the problem continues. Thanks
Private Sub cmdGetReport_Click()
On Error GoTo Err_Handler
MsgBox ExportRequest, vbInformation, "Finished"
DoCmd.Close acForm, "frmMonthEndRpt"
Application.FollowHyperlink "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOOutput.xls"
exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical, "Error"
Resume exit_Here
End Sub
Public Function ExportRequest() As String
' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef ' Added 10/06/07
Dim sSql As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
Dim strMacroName As String
Dim strFileName As String
strMacroName = "DeleteBlank"
strFileName = "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOOutput.xls"
Const cTabOne As Byte = 1
Const cStartRow As Byte = 4
Const cStartColumn As Byte = 1
DoCmd.Hourglass True
' set to break on all errors
Application.SetOption "Error Trapping", 0
' start with a clean file built from the template file
'sTemplate = CurrentProject.Path & Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOTemplate.xls"
sTemplate = "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOTemplate.xls"
sOutput = "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOOutput.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput
' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabOne)
sSql = "select * from qryAOSummary"
Set dbs = CurrentDb
Set qd = dbs.QueryDefs("qryAOSummary")
qd.Parameters![txtStartDate] = [Forms]![frmMonthEndRpt]![txtStartDate]
qd.Parameters![txtEndDate] = [Forms]![frmMonthEndRpt]![txtEndDate]
Set rst = qd.OpenRecordset
If Not rst.BOF Then rst.MoveFirst
' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to AoOutput.xls"
Me.Repaint
For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)
If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If
wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next
wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop
ExportRequest = "Total of " & lRecords & " rows processed."
Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."
'Inserts Month,Year format based on entry in start date field.
wks.Range("A2").Value = [Forms]![frmMonthEndRpt]![txtStartDate]
'The Application.Run will run the Macro(s) that you saved in your spreadsheet
wks.Application.Run "'" & strFileName & "'!" & strMacroName
exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function
End Function
View 1 Replies
View Related
Jun 19, 2007
If I have the following value in a cell:
Joe <100,894> Doe
Is there a function in Access that will clear out the <100,894> leaving me with Joe Doe? To my understanding the Replace function only can replace certain characters. How can I delete everything in between the < > as well?
Thanks,
Paul
View 4 Replies
View Related
Feb 11, 2008
I have an order form that requires me to change the current promotion we're running continually. I need to populate all rows based on the update changes of one textbox. Is this possible? Right now when I update the promotion text box, it only updates the text for row one.
View 1 Replies
View Related
Nov 7, 2007
hello all, any help greatly appreciated.....
I have two tables in the same Access 2003 database.
Table1 is called "Clients" and has address type fields, e.g. street_name, postcode, etc. It also has a field called CYPAN_area.
Table 2 is called CYPAN_Postcodes and has two fields; postcodes and the CYPAN area that postcode belongs to.
I want to create a function so that when someone enters the postcode in table1, the database searches Table2 and identifies which CYPAN area that postcode belongs to and automatically populates the "CYPAN_area" field in Table 1.
any tips greatly appreciated
Harry
PS I'm only a novice so I don't know anything about VBA etc, but I understand excel type formula
View 3 Replies
View Related
Jul 15, 2005
I want a quick-n-dirty method of filling char or numeric fields on a form with leading zeros. Can I do that without VBA?
View 3 Replies
View Related
Nov 21, 2005
I'm kinda new at ACCESS, and I've accepted an assignment that is giving me some trouble. For beginners, I could use some help as follows:
I am constructing a data entry form that is keyed on PID (Personal ID). I would like to be able to have certain fields in my form (Name, Title, Department, etc.) fill in automatically from information already stored in a different table, also containing the PID (which I can set as key). I would like this action to occur either when the PID field has reached 3 characters or when the PID field has LostFocus.
Thanks for any help in advance.
MAE:
View 2 Replies
View Related
Jan 16, 2006
Is it possible to have some fields filled in automatically as soon as a certain value has been entered into another field. e.g. if a town is entered in one field, then the postcode is automatically entered into the next field.
View 1 Replies
View Related
Apr 24, 2014
I'm wondering if there is a way in Access 2013 to - for example - enter a value in a date field and then highlight it and drag it down to populate all the records in the table with that value, as can be done in Excel?
View 2 Replies
View Related
Jan 11, 2007
This one has me stumped:When a particular database opens, its window is partially under (hidden by) the menu and tool bar. I can't click on the window's top in order to move it. I've tried several things (i.e. resizing the screen resolution, moving the menu and tool bars, resizing the window, etc.) with no resolution. Any suggestions.Am using Access 2003, Thanks, Joel
View 1 Replies
View Related
Mar 12, 2005
I am new to Acces programming and have been tasked with making a database for work. I is a very simple database. What I need it to do though that I can't figure out is. Once the table is populated with information I want the user to be able to select a record from my combo box and then all the rest of the information from the record will automatically display in the correct text field so any changes can be made to it. So I have say Combo Box John Smith and when he is selected I want text field DOB to automatically fill with 01/01/1960 as that is what was put in there when he was first entered.
Thanks.
View 1 Replies
View Related
Dec 16, 2005
I am new to Access, but have an urgent need. I want to have certain fields (Yes/No type) to be selected based on a certain field (also a Yes/No) being selected.
It's very straight forward. Select West Coast, and the states CA, OR, and WA are selected. De-Select West Coast and the states CA, OR and WA are deselected.
Any thoughts? thanks in advance.
Warren
View 3 Replies
View Related
Feb 15, 2006
Is it possible to connect data so that when one field of a record is entered the rest of the fields associated with that first field of the record automatically fill in?
Example: If I were to enter a new contact record name in the name field I want the corresponding fields of address, city, state, zip, phone....etc. to fill in automatically.
View 4 Replies
View Related
Oct 5, 2006
I have the fields City, State and Postcode in my Member's table. My Member's form has the same 3 fields.
I have the CityLookup table which contains the City name and relevant State and Postcode.
I want my users to be able to either select from the combo dropdown list to select a City or start typing the name and the name will be autofilled from the list - then the correct City name, State and Poscode will automatically fill the fields on my form.
i have looked at a number of combo box methods and can not get one to work for me.
Any help would be appreciated.
View 5 Replies
View Related
Jul 22, 2013
The database needs to have columns for 17 individual connectors. These fields are populated from another table containing all of the possible connectors that could be used and a description of them. I need to set up a query that contains the 17 connector fields as well a field with the description of each connector. Is it possible to have this query auto populate the description fields from the table that contains the connectors. I have attached a picture below.... Each pin has a label that has to be manually filled and a pin name that has to be chosen from another table. I want to have the pin description fields auto populate to match the selected pin names.
View 4 Replies
View Related
Nov 30, 2005
Hi!
I have a table with several fields. I would like to have a lookup in one field (combo list - get data from another table field) and for 2 other related field have the coressponding data appear automatically according to the selection in the lookup field.
eg.
Table1
|Field1...................|Field2...............|F ield3...............|
|LookupField1Table2 |AutoField2Table2|AutoField3Table2|
Table2
Field1...|Field2... |Field3...|
Data1...|Data2...|Data3...|
I am not quite sure if my question is clear... I had trouble in describing it in my post...
View 2 Replies
View Related
Jun 15, 2007
Let's say in one table (called Employees) I have two columns, Name (the Primary Key) and Job (Both are text fields). One row contains "Jack" and "Gardener" in each column respectively. If I were to have a form for a different table where I select Jack's name from a drop down menu of all the "Names" in "Employees" and it then automatically fill in the next field with his Job (which would be "Gardener").
I really hope I'm making sense... Is this possible?
View 14 Replies
View Related
Jun 10, 2013
I have a form (Project Form) with (Project_ID,Applicant_ID,Project_description, etc). To make it easier for the user who may not know the Applicant _ID when he/she is adding a new project for the applicant, I want to put a combo box with the Applicant Names in it, and once the Applicant Name is chosen, the Applicant ID will be filled out automatically and be saved in the Project Table.
My Approach so far was adding a combo box with two columns(Applicant ID, Applicant Name), and basically adding the following code:
Private Sub Combo36_AfterUpdate()
Me.Applicant_ID = Me.Combo36.Column(0)
End Sub
This approach works well, when I select an applicant, the Applicant ID will pop up correctly. However, This ID is not being saved in the Project Table.
View 2 Replies
View Related
Sep 24, 2013
I have a data entry form feeding a table named [Group Members] using Access 2010.
It has 4 fields; [Group ID] & [Group Name] derived from tbl.[Groups] and [Member ID] & [Member Name] derived from tbl.[Mail List]. The 'ID' fields are foreign keys from the respective tables.
The 2 'Name' fields have drop down lists for input selection, but my requirement is to cause auto fill of the 'ID' fields when the respective 'Names' are selected.
I had assumed that this simple requirement could be achieved with a suitable control on the form property sheet. It is so simple to do in Excel!!
View 3 Replies
View Related
Jan 10, 2012
is there a way to have a form set to either auto populate fields based on the FIRST FIELD? For example: if the user type in the STUDY ID, the other fields like (first name, last name etc..) are auto-populated/auto-filled? and if the STUDY ID is new and not in the table, then the users simply fill in all the fields/complete the form and save? And I would also like a message box to show when I am typing the STUDY ID that would say something like: this person is on the system already. Click YES for this person and NO to add the user?...
View 2 Replies
View Related
Dec 6, 2014
I have a primary school database. I'm trying to create a form that allows a teacher to select their class, then select a subject and then be presented with a data entry form in a table layout that lists only their student's names in one column and an empty column to input results for the selected subject.
To simplify my explanation to just three tables, lets say my tables are:
> Students....which stores student names plus a foreign key for their class
> Classes...which stores the class name
> Results...which stores all the results (fields are: ResultID (key), StudentID, SubjectID, Result, DateofResult)
I have no problems creating reports where the teacher selects their class from a combo box to generate a report based on a crosstab query. But this one has me stumped.
View 2 Replies
View Related
Dec 17, 2007
Is there an easy way in Data access pages to do like you do in forms as far as using a dropdown list and when you choose a name/acct#/rec# or whatever, that the rest of the fields are automatically updated to match? Just curious.
If you need an example, I can provide one.... basically, I'm trying to get a data-acess-page to act like a form so someone without access can do a simple search.
View 1 Replies
View Related
Nov 9, 2012
I have a Table named TBLBookings...on one of the fields I have a Lookup wizard thats linked to a Table named TBLVehicles which includes
Car Reg
Car Type
Location
When I run the TBLBooking and click the drop down list it shows up
Car Reg
Car Type
Location
but once the field is clicked all it shows in the records is the REG where in a form id like to see what vehicle it is location etc...is it possible to have extra fields in the form named Car Type and Location and once the registration is chosen it automatically fills in the correct details for them?
View 4 Replies
View Related
Mar 21, 2012
I am trying to auto-fill address info from a combo field. I'm using a select query on a table for the information. It appears to work for the first field (Firm Address1), but then stops working on all the other fields.
View 3 Replies
View Related
