Code To Populate Multiple Fields In One Sub Form From Another

I am looking to add multiple lines of record to a sub-form via a module activated from another linked sub-form.
As an example of what I want to do:
On identifying a particular type of vehicle servicing (e.g. 6000 mile service, 12000 mile service) on a service event sub-form of a vehicle form, I want to enter a list of required parts (e.g. oil filter, oil, etc) on another sub-form called "parts", that I can then edit before recording in an underlying table.
Can anyone help.


Using A Query To Populate Fields In A Form??

I have very minimal knowledge of MS Access and I have to create a database to input, update and display project information. How do I write a query to populate fields on a form, from either a single table or multiple tables?

View Replies View Related

Using A Table To Populate 3 Form Fields


I have a form with 3 combo boxes part No, Description, Cost. I have created a table with this information in I want to be able to select the information from either one of the three drop down boxes and the other two to automatically collect the information from the other colums of the table. Can anyone help please?

View Replies View Related

Populate Form Fields On Open

Hi all,

I'm sure this must be a well used function by many access users but I can't find anything about it online at all - please help!

I want to open a subform "AddReservation" and populate three fields with data taken from the relevant record in the main form. So in the btnOpenAddReservation_Click event to open the subform I set global variables, then in the form_open event I have this:

Private Sub Form_Open(Cancel As Integer)

Me.CatID = stLinkCatID
Me.CatName = stLinkCatName
Me.OwnerID = stLinkOwnerID

End Sub

But I get the error 2448 'Can't assign a value to this object'.

However if I put the same three lines into the start of the subform's btnARSave_Click event it works fine:

Private Sub btnARSave_Click()
On Error GoTo Err_btnARSave_Click

Dim stDocName As String
Dim stDocName2 As String
stDocName = "OwnersAndCats"
stDocName2 = "AddReservation"

Me.CatID = stLinkCatID
Me.CatName = stLinkCatName
Me.OwnerID = stLinkOwnerID

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

YesNo = MsgBox("This reservation has been added successfully, do you want to add another?", vbYesNo + vbQuestion, "Add More Reservations?")
Select Case YesNo
Case vbYes
DoCmd.GoToRecord , , acNext
Case vbNo
DoCmd.Close acForm, stDocName2
DoCmd.Close acForm, stDocName
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acGoTo, stRecordNo
End Select

Exit Sub

MsgBox Err.Description
Resume Exit_btnARSave_Click

End Sub

What am I doing wrong? Why can't I populate the fields on open?

Many thanks

View Replies View Related

Populate Form Fields From A Combo

Trying to update fields (bound) on a form (built using the wizard) from value selected in a combo box. I can get this to work with unbound text boxes using an After Update event handler and a code query along the lines of:

Me!TxtJobCode1 = Me!CboJobDesc.Column(1)

But this doesn't work where the form field is bound to a table.

So qu is - how do I achieve the same result using table fields instead of text boxes?

I could use text boxes but not sure then how I can save that data as a record in the table if it is not bound to fields.

Any help appreciated


View Replies View Related

Auto Populate Fields On Current Form

Hi. I tried to find out if this issue was already posted, but did not find. If anyone can help me, I am trying to auto populate two fields on a subform. The field I want to populate is the description of a role. So if the user selects the role name from the combo box, the description field will automatically update. Thanks.

View Replies View Related

Populate Unbound Fields On A Continuous Form

Can I populate unbound fields on a continious form, if it is possible would you give me some code to look at or direct me to an article


View Replies View Related

Populate Fields On Form - Version: 2000 (9.0)

I am new. I need to populate multiple fields on my form based on what is selected from combo box. And have a second manual entry form populate with records in the underlying table. Thank you.

View Replies View Related

Using Code To Populate Form Text Box - Version: 2003 (11.0)

I'm still new at this, and can't figure this out.

I have a subform who's record source is a junction table. I use the subform to enter the desired information into the junction table. The subform uses a combo box to select the name of a partnership to enter. The combo box's record source is one of the joined tables. Another field on the subform is a text box for the Partnership EIN.

I'm trying to develope coding so that if a Partnership is chosen from the combo box list, and it is equal to the partnership name in the joined table, take the associated EIN that is in the joined table and place it in the field for the Partnership EIN on the subform. What I've come up with so far is:

Private Sub Partnership_Name_AfterUpdate()
Dim strPartnershipEIN
Dim strPartnershipEIN_Detail
Dim strPartnershipName_Information
Dim strPartnershipName_Detail

strPartnershipName_Detail = Form_PartnerShipDetail.Partnership_Name
strPartnershipName_Information = Form_PartnershipInformation.PartnershipName
strPartnershipEIN = Form_PartnershipInformation.Partnership_EIN
strPartnershipEIN_Detail = Form_PartnerShipDetail.Partnership_EIN

If strPartnershipName_Detail = strPartnershipName_Information Then
strPartnershipEIN_Detail = strPartnershipEIN
End If

End Sub

It doesn't work, and I don't know why. Am I on the right track, or way out in left field?

View Replies View Related

Auto-populate Fields In A Form Using A Command Button

Can someone help me figure out how I can create a command button on a form that will prompt the user to enter a file # (or similar data) and that will auto-populate all the fields in the form from the underlying table? I hope this makes sense. Any and all help is greatly appreciated.

View Replies View Related

VB Code To Auto Populate Info In A Form Field - Version: 2003 (11.0)


I am trying to create a VB command in my fProgram Requests Form where I can enter an address (from tCensus Table) so the census tract number(from TCensus Table) will automatically appear in the Census field. Is this possible? Are there any other ways to approach this?

Appreciate it!

View Replies View Related

Code Issue - Need To Populate Text Boxes On A Form With Info Fro - Version: 2003 (11.0)

I have been working on this code and I hope I'm on the right track. I am getting errors.

I have a form that has a command button that opens another form. When the new form opens, I want some of the text boxes to be populated with information from the previous form (by default). The frmOperatorInfo contains the information that will automatically populate the text boxes in frmSurvey. The following code is for On Open event for frmSurvey.

Private Sub Form_Open(Cancel As Integer, Cancel As Integer, Cancel As Integer, Cancel As Integer)
Dim OperatorID As Long
Dim LastName As String
Dim SurveyDate As Date
Dim MachineType As String

'When frmSurvey1 is opened, the values from frmOperatorInfo
'contain values for OperatorID, LastName, SurveyDate, and
'MachineType will synchronize and populate in frmSurvey1
OperatorID = Me.OpenArgs
LastName = Me.OpenArgs
SurveyDate = Me.OpenArgs
MachineType = Me.OpenArgs

Dim RS As DAO.Recordset
Set RS = Me.RecordsetClone
RS.FindFirst "OperatorID = '" & OperatorID & "'", "LastName = '" & LastName & "'", "SurveyDate = '" & SurveyDate & "'", "MachineType = '" & MachineType & "'"
If Not RS.NoMatch Then
Me.Bookmark = RS.Bookmark
End If
End Sub

Please let me know if I'm on the right track and what I need to change in the code.

Thanks much.

View Replies View Related

Multiple Fields On New Form?

I have 3 tables
ItemTable, ItemCompositionTable, CompositionTable

The user can bundle a few items together from ItemTable and name this item composition which is stored in ItemCompositionTable. They can then bundle these ItemCompositions together to create a "bundle of bundles" so to speak.
This all works fine.

My question is this:
If i have a combox box with a list of the final compositions, is there a way to press a button and see a list of all the items and their data so the user can change them?

any help would be great..questions? ask away!

View Replies View Related

One Table, One Form, Multiple Fields

Hello everyone,

I was working on a database several months ago and I came across this forum while troubleshooting. I had to abandon the incomplete project for some time now, but I am back and ready to get this thing up and running!

My database used to be comprised of lookup tables and other things for each of the fields in my main table, but I have recently scratched that idea for simply using list boxes.

My main problem, I believe, is with my form. I am trying to have two combo boxes, a beginning date and end date, and a keyword text box, all used as criteria in a query.

Seems pretty simple, and I actually got the keyword text box to work, but the combo boxes and between dates critiria return all records.

Can someone double-check my work? I'm sure it's just a simple error, but I will attach a stripped-down database for anyone to look through.

Thanks ahead of time for anyones help, it is greatly appreciated!

View Replies View Related

Multiple Query Fields Of A Form

Hello everyone!

I am new to MS Access. I am creating a small database for motor vehicle clearance. I have a SEARCH form where i want to put the fields for searching query table:


REF No.:


I need it in such a way that even when only one of the above fields is supplied with data it should still return correct data from the query table.

For dates (BEGIN DATE, END DATE) i tried: Between [forms]![search]![begin date] and [forms]![search]![end date]

This worked well when placed alone of the SEARCH form. But when i added more fields (VEHICLE MAKE, REF No.) the query returned nothing but a blank query table.

How can i possibly resolve this.

I will appreciated.

View Replies View Related

Summing Across Multiple Fields In A Form

I am trying to create simple database of golfers and points they earn in seven events. Each event is its own field.

What I would like to do within a form, after entering the points earned in each event, have the form calculate the total points and populate that into a table. For some reason I can't seem to figure out how to sum totals across multiple fields.

The next step would be to have a way to sort in a form or report (i'm more familiar with forms) by points. That is open the form and it shows the top ten golfers and the points they have earned in descending order.

Any help that can be provided would be very appreciated!



View Replies View Related

1 Form, 2 Tables, 1 Field To Multiple Fields

Please be kind, i have little VB Knowledge, and wish to expand my learning on this topic.

I have a form that updates information on one table, and has a subform displaying info from another table.

the subform is filtered, and only shows data from what is specified from the filter of the main form.

If I update information on the main form for instance,
field 1, (the data on the subform has the same data so there is the relation), how do i update the subform by only updating the mainform? can this be done through some VB or something?

if you folks out there can give a Smidget of info on which VB codes i can use i can pretty much figure it out.

like Docmd.write something?

View Replies View Related

Search Multiple Text Fields In A Table From A Form

Please bare with me as I am an Absolute Access VB code novice . I have a Form in my Access DB and I found the Following code on this site and placed it behind my search button :

Private Sub SearchButton_Click()
DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "'"

End Sub

The Search works great but only searches on the 1 Field "First Name" . How would I expand the VB code to include another 2 fields within my table . ( the other 2 fields I want to serach appear on the Form ) .

View Replies View Related

Search Multiple Fields In Form - Version: 2003 (11.0)

Hi there,

I have created a search form, which is based on the Sample Search Form that kapeller had previously posted, and it has been working great, however I now need to adapt it a bit further.

Currently I have two fields "Primary Trade" and "Secondary Trade"

When I enter something in the primary trade box and click search, it just searchs the primary trade field with the following code:

If Me![GenTrade] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenTrade like """ & GenTrade & "*"""
End If

And likewise if I select something from the Secondary Trade field and hit search it searches using this code:

If Me![GenTrade2] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenTrade2 like """ & GenTrade2 & "*"""
End If

It returns the results using the code:

If Nz(DCount("*", "qrySearchCriteriaSubbie2", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
Ssql = "SELECT DISTINCT [GenCompanyName],[GenFirstName], [GenLastName],[GenPostalCity], [GenTrade], [GenTrade2], [genothertrade], [GenDivision], [CorrFunction], [CorrCC], [Corrother1text], [CorrOther2text] from qrysearchcriteriasubbie2 " & sCriteria
Forms![frmSearchCriteriaSubbie]![frmSearchCriteriaSubbie2].Form.RecordSource = Ssql
MsgBox "I'm Sorry, the search failed to find any records that match." & vbCr & vbCr & _
"Please try again!", vbOKOnly + vbQuestion, "Search Record"
Call subClearFields
End If

Is it possible to get it to search both fields at the same time? So if someone enters something into primary trade it also searches secondary trade as well and returns a result if there is information in either of the fields???

I have tried adapting the code several ways but still can't get it to work, so figure someone here will be able to help me out.

Thanks in advance


View Replies View Related

From Code To Populate A Recordset


I am trying to populate a recordset in
Access97 using the following code, but cannot
seem to get it to work.

The table name is "EventData" that has Type and date fields.
startdate and enddate are text boxes on a Form, for wich the user
enters the date range, and then clicks a command button to
build an Excel spreadsheet on the fly that will be populated with the
results of the recordseet.

here is the code:

Dim db As DAO.Database
Dim rst As DAO.Recordset

strSql = " SELECT EventData.EventType, Count([EventData].[EventType]) AS [Num Occurrences] FROM EventData WHERE ((EventData.Date) >= (Me!startdate)) GROUP BY EventData.EventType"

The error message that I am getting is "run time error 3061", too few paramaters. ALSO not sure if I need a semicolon right before the closing quote of strSql.

Any help is appreciated.

View Replies View Related

Query Linked To Form With Textbox To Search Multiple Fields

Right what im after sounds complicated but its quite simple.

I have a client database, in this database each client has 3 skill fields, i also have a form with a listbox and an unbound textboxes that are linked to the query so when the textbox is typed in the listbox updates.

Now ive sorted out the problem of not all fields showing it some of them are blank now the problem is.

When input text into one of the boxes the listupdates, now if theres text in two boxes. and theres data in one of each that matches it will show neither, because the query is looking for data that matches in both fields, now i need partial matches so it will give results of data in both fields.

not understanding fully what i mean?

i have zipped my db, if you open the db then open the form 'MainListRecTrades2nd' and fill in data in both primary and secondary trades that matches from the list you will see.

The solution i was thinking of would to have 1 textbox that searches all the fields and displays data from any of the fields in the list

Thanks for any help

View Replies View Related

Display Multiple Fields From A Table In A Form - Version: 2002 (10.0) XP

I've been reading the forums for a few weeks and the info has been immensely helpful - however, I'm a fairly new access user and have a question I haven't found an answer to yet.

I have a form that I will be printing to line up employees with projects to work on (automobile parts) - the form looks at several tables, some cascaded (parts, employees, etc). I would like the form to contain fields populated automatically by selecting a combo box. I have a table name "Parts" which contains part name and part number and a field from another table name Plants, which contains stamping plants. My question is: If I select an item from a combo box (the part number) how can I automatically populate a label or text field in the form with the part name?

View Replies View Related

Fields With Multiple Control Sources On One Form - Version: 2007 (12.0)

This is probably a simple question, but I can't find the answer. I have a form. Some fields on the form I want to have a certain table as their control source, while other fields on the form I want to have a different control source. I don't want to use a sub form. Is that possible?

View Replies View Related

Changing Fields To Must Populate

Hi Everyone

I need to change a fields properties on a form depending on another fields data.

i.e. If a user ticks a Yes/No box saying they've done something they must also complete the date they did it. I can't set the date field in the table to be 'Required' as if ther is no tick then it doesn't need to be.



View Replies View Related

Add 3 Record Fields To Populate A 4th?

I have a table where there each record has 3 fields i need to add together for a fourth both in my form and the table and i need the data in that field to be uneditable. how might I accomplish this? In the control source i tried:


but it displays


View Replies View Related

Populate 3 Fields From One Combo Box

I have a combo box on a form that shows 3 columbs from tblparts in the drop down, I wish to select one row from the drop down, that will enter the selected data in three fields on the form.

For example my form has three fields "part code", "Description", "cost".

Part Code = combo box
Description = Text Box
Cost = Text box

I want to select the data from the combo drop down to populate all three boxes.

Can sombody please help, as I have been stuck on this all afternoon. Cheers

View Replies View Related

Auto Populate Text Fields

I am trying to auto populate a text box when i click on the button to add a new record. This is the code for the addnewsequence button.

Private Sub addnewsequence_Click()
On Error GoTo Err_addnewsequence_Click

DoCmd.GoToRecord , , acNewRec

Exit Sub

MsgBox Err.Description
Resume Exit_addnewsequence_Click

End Sub

In this form is a text box called degree name that is link from the table degree. It autopopulates with the degree upon the sequence form first opening button clears the text box when i click add new sequence

any help greatly appreciated...
Thank you

View Replies View Related

Populate FK Field With Code? - Version: 2003 (11.0)

Hi everyone.

I'm having a problem with my code. Hope someone can help.

This concerns two tables. tblCountry and tblProjectMain. tblCountry is the parent table.

I'm using a combo box to select a country which in turn I can allocate a Project to.
when the country is selected I'll press the Cmd and it will add the project to tblProjectMain.

If the Country already has a project within the tblProjectMain I can use the Cmd to allocate another project so for example
Country: Uganda. Project: Uga 002 (002 being the second project within that country.)
If it's a new country and there hasn't been a record created for the tblProjecMain yet and I try and execute the Cmd I get "94, invalid use of Null"
I believe this is because the code doesnt add the FK to tblProjectMain.

Does anyone know how I add this within the code so I can execute the Cmd?

Thankyou for any help in advance.

This is the code if it helps.

Dim strCountry As String, strNum As String, strSuffix As String, strFullCode As String
Dim strWhere As String

strCountry = Me.cboPrefixCountry
strWhere = "[PrefixCountry]='" & strCountry & "'"
strNum = DMax("ProjectCode", "TblProjectMain", strWhere)
If strNum <> "" Then
strNum = strNum + 1
strNum = 1
End If
strNum = Format(strNum, "000")
strSuffix = 1
strFullCode = strCountry & " - " & strNum & "/" & strSuffix

Me.txtNewProjectCode = strFullCode
Me.txtNewProjectCode.Visible = True
Me.cmdAccept.Visible = True

(the Projectcode is the Project number for that country (001) etc.)
(I've ommited the Error handling for space!)

View Replies View Related

Extract Data From Field To Populate Other Fields

I have read some tutorials online on how to split data into multiple fields.

While this is the gist of what I need - it is much more complex.

I am dealing with adresses.

This inherited database has 2 fields - ZIP, and ADDRESS. I need to extract the data within the ADDRESS field to populate other more detailed named fields.

I would like to ultimately have my fileds to be: STREETNUM, STPREDIR, STREETNAME, STREETSUFX, SUD, SNUM.

Example: (Current ADDRESS data)

512 N Manchester Dr Ste 201

(extract ADDRESS and import into)

STREETNAME = Manchester
SUD = Ste
SNUM = 201


Now I have tried several queries to extract the first bit of data 'STREETNUM', using the example on the link I posted above. INSTR, RIGHT, MID, LEFT.

Problem is there are also P.O. Boxes formatted like: PO BOX 1234, so my STREETNUM column includes the PO, when it should include the 1234.

PO Box 1234 should translate to:


All the rest of the fields aren't needed.

As you can see, I'm in OVER my head.

I would greatly appreciate any assistance or direction in accomplishing this task.

I am not a beginner with Access, but I am far from being an expert. A breakdown of some complicated tasks may be necessary... :-)

Thanks in advanced.

View Replies View Related

Populate Subform/table With Multiple Records

I have the following tables:
ProjectsID (Primary key - exclusive)
ProjectsDetailsID (Primary key)
ClientProjectsDetails (Secondary key)
QuotesID (Primary key - exclusive)
QuotesDetailsID (Primary key)
ClientQuotes (Secondary key)

I have the following forms:
fmProjectsDetails (parent/child)
fmQuotesDetails (parent/child)

When I add a project I need to have data from the tblQuotes and tblQuotesDetails to populate tblProjects and tblProjectsDetails respectively.

It is very easy to populate tblProjects from tblQuotes since it is a single record. Therefore I use:
Me.ClientProjects = Me.ClientQuotes

The question is: how to match the data of the 2 subforms? Since there will be more than one record per subform?

Any help is really appreciated.

View Replies View Related

Populate Multiple Records With Same Data In Same Field?

Howdy.....I'd like to know if there is a way to populate the same field
in several hundred records with the same text value instead of typing into each field in datasheet or form view. Macro?

Thanks for your support!


View Replies View Related

Populate Excel Code Truncates.... Why? - Version: 2002 (10.0) XP


I have the below code in a module, and when I run it, although the recordset displays the full data in memo fields, when it is put into an excel cell, the data is truncated. I don't understand why it is happening. Further, I have tested that if I attempt to fill the excel cell with a text string that is more than 255 chars, it works, which just confuses me more!

Any help would be appreciated...

Public Sub TransferDataToExcel(strQueryString As String)

'On Error GoTo Me_Err

Dim objApp As Excel.Application
Dim objBook As Excel.Workbook

Set objBook = Workbooks.Add
Set objApp = objBook.Parent

Dim rstTable As ADODB.recordset
Set rstTable = New ADODB.recordset

rstTable.Open strQueryString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rstTable.RecordCount <= 0 Then
MsgBox "There was an error when trying to retrieve the record count."
GoTo Cancel
End If

If rstTable.RecordCount = 0 Then
MsgBox "There are no records to export"
GoTo Cancel
End If

Dim intA As Integer, intB As Integer

If rstTable.RecordCount > 0 Then

With objBook.Worksheets(1)

'Do column headings, set row to 1
intB = 1
For intA = 0 To rstTable.Fields.Count - 1
.Cells(intB, intA + 1).value = rstTable.Fields(intA).value
.Cells(intB, intA + 1).Font.Bold = True
.Cells(intB, intA + 1).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Cells(intB, intA + 1).WrapText = False
Next intA

'Fill records, start at row 2
intB = 2
Do Until rstTable.EOF = True
DoCmd.Echo True, "Building row " & intB - 1 & " of " & rstTable.RecordCount
For intA = 0 To rstTable.Fields.Count - 1
.Cells(intB, intA + 1).value = rstTable.Fields(intA).value
.Cells(intB, intA + 1).WrapText = False
Next intA
intB = intB + 1

'Do column formatting
For intA = 0 To rstTable.Fields.Count - 1
.Columns(intA + 1).EntireColumn.AutoFit
Next intA

'Set zoom/window settings
objApp.ActiveWindow.Zoom = 85

End With
End If

Set rstTable = Nothing
objApp.Visible = True
Set objBook = Nothing
Set objApp = Nothing
Exit Sub

On Error Resume Next
objBook.Close False
Set objBook = Nothing
Set objApp = Nothing
Set rstTable = Nothing

Exit Sub

MsgBox "An error occurred:" & vbCrLf & Err.Description, vbExclamation, Err.Number
GoTo Cancel

End Sub

View Replies View Related

2 Forms On 1 Page. (and Having A List Box Populate A Bunch Of Fields)

You veterans have probably heard these questions many times, I've had a search around but couldn't find quite what i was after.

This is my first database.

I have 2 tables, one for customer details, and the other is products (holiday packages)

I have made a product form for staff to input new holiday packages. And for the customer form this is also the order form, so a staff member picks up the phone and gets customer details, name, ph, etc and then asks what holiday package they want. I want this to be a drop down list that always fetches all the packages availiable form the product table and when you choose a package it shows all the details on that package.

I need this all to be on the 1 form, if the staff member has to swap back and forth between customer form and product form to get info on a product this will severly hamper productivity.

If my question is abit hard to understand i can take screen shots of my tables/forms to get a better idea. And if someone can really help me finish this database off i'll pay them somethign for there trouble.

Regards, Chris.

View Replies View Related

Copyrights 2005-15, All rights reserved