Over 1 million tech questions and answers.

ACCESS: Form shows record for each record in subform

Q: ACCESS: Form shows record for each record in subform

Hello All,
I have this database I built for all my music CDs. It is searchable by artist, date recorded, venue, city , state, notes and track title. The search works fine but I have one issue. I use a subform for all the track titles and it displays the correct songs for the CD. However, I get a record for each song. So if one CD has 16 songs I get 16 records for that CD. In my sample dB I have 7 CDs but it shows 117 records because that is the sum of all the songs on those 7 CDs. I want it to show only the 7 CD records and not a record for each song and still be searchable. I can make it show the way I want but then I can't search by song title. I attached my condensed database version to look at in case my description seems a bit convoluted. Thanks.

RELEVANCY SCORE 200
Preferred Solution: ACCESS: Form shows record for each record in subform

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: ACCESS: Form shows record for each record in subform

Read other 6 answers
RELEVANCY SCORE 147.2

I want to create a form in a sort inbox style so i ahve a list of records at the top in a datasheet and then by clicking on the list the details should show up in colunmar view below.

Sound simple its not.

I can get it to work the other way round by having a datasheet subform in my main form but thats not much use.

What i need to do is to somehow reverse how the form subform relationship works.

Any ideas?
 

A:Access: Form with subform datasheet. Selecting record on datasheet shows in form.

Read other 12 answers
RELEVANCY SCORE 121.2

Hello again! I'm continuing to have problems with a form I created in Access 2007. I have a master form with 2 sync'd combo boxes. The 2nd combo box is supposed to display results from a query into a continuous form (sub form). The problem I am having now is that the continuous form is only displaying a single record, even though there should be several resulting from the query.
Another - possibly related problem - is with the combo boxes. When you open the form, the first box (cboRegion) is empty but the 2nd (cboLocation) still shows the last city name chosen. The continuous form also shows the results of the last search, but still only one record.
Any thoughts are appreciated!
 

A:Solved: Access 2007 - Continuous form only shows single record

Read other 16 answers
RELEVANCY SCORE 117.6

I though this would be simple but searches have not worked. here is my code from the caling form:

Private Sub BD_stuff_Click()
On Error GoTo Err_BD_stuff_Click
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = CurrentRecord
stDocName = "RFIsBD_stuff"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_BD_stuff_Click:
Exit Sub
Err_BD_stuff_Click:
MsgBox Err.Description
Resume Exit_BD_stuff_Click
End Sub

I thought defining the "stLinkCriteria" would work but it did not. I've tried severa other things from on line searches but nothing has worked the subform always is opened to the 1st record.

A:Open subform that has the current record of calling form at opening

I don't understand the context for this question. Searching this forum, from your hard drive, from an application?

Read other 5 answers
RELEVANCY SCORE 110.8

Hi, I have an access 2007 database where I have a main form and several subforms in tabs.

I would like to have the user be able to "Add a Record" to the subform. The simple "Add record" from the button wizard does not work. What do I need to do to add a record to the subform?

Thanks!
 

A:Access 2007 - Add record to a subform

Read other 16 answers
RELEVANCY SCORE 110.8

I am running Windows XP with Office 2007, and my problem is specifically within Access.

I have a main form [YOY Main Form 1] that simply allows selection of an area (geographically) from a combo box.

I then have a linked subform [YOY Sub Form 1] that displays customers that are within the chosen area, and I have correctly linked the child/master links.

When the subform is open I want to be able to click on any customer and have a separate form open that displays information about the customer. This 3rd form is not linked or embedded in anyway, it is simply another form based on another query, but in the query pane I have set the criteria to [Forms]![YOY Sub Form 1]![Account Code].

The subform is set to continuous forms with record selectors set to Yes.

The problem I have is that when I click on the individual customer record, instead of the form opening automatically, I have a dialogue box prompting me for the Account code. If I enter my cust code the form opens as it should, but I would like this 3rd form to open automatically simply by clicking on the customer record in the subform.

Can anyone help please?
 

A:MS Access Record Selection from Subform

That is because you haven't included the mainform in the Criteria Row, this

[Forms]![YOY Sub Form 1]![Account Code]

should be

[Forms]![YOY Main Form 1]![YOY Sub Form 1]![Account Code]

You can also use Criteria in the VBA of the Command Button that Opens the Form as well
 

Read other 2 answers
RELEVANCY SCORE 109.6

I have a form with a subform. When I open the form, I want the subform to display a new record. How do I make that happen? If I use a macro to "open new record" when the form opens, it displays a new record for the form as well as the subform. I want only the subform to display a new record.
 

A:Open Subform with new record - Access 2003

I am not quite sure about the logic of what you want to do, I would have thought you would want to see historical records, but there are 2 ways to do what you want one is to set the Subform's "Data Entry" property to "Yes", this means that it can only accept new records and display those entered since the Form was opened, not older records.
The other involves using VBA code in the Subform's "On Load" event procedure which says
me.newrecord

The VBA method should still have the old records available, just move to a new record.
 

Read other 3 answers
RELEVANCY SCORE 109.6

I'm just learning Access and am setting up a database where the Form has customer information and the subform shows their order details. There are only two fields in the subform, Product Name and Quantity. The product names are listed in a combo box. How do I enter more then one product order? I can add one entry but I need it so when I hit enter it gives me another line to enter another order.
 

A:Access 2003 Adding Record in SubForm

In the SubForm's Default View property set it to "Continuous Forms"
 

Read other 1 answers
RELEVANCY SCORE 108.8

Hi:

I've created a form in Access-2013 and placed record navigation buttons (using the button wizard) in the footer to allow the user to scroll through the records. They are the usual buttons and function as you would expect:

<< | < | > | >>[/FONT]

Problem is that when a user is on the last record in the table and clicks the ''right" button (in red above), the form displays a blank record. This is normal behavior for Access (as far as I know) and as long as a user enters NO DATA in ANY field, Access does not write the record to the table.

Given that users may be less than astute , I would like to eliminate the possibility of them creating a "blank" record in the table.

Is there anyway to stop this default behavior in Access?

Thanks
RonK
 

A:Solved: Stopping a form from going past the last record when using record navigation

Read other 10 answers
RELEVANCY SCORE 108.4

I have a db that has 5 tables each linked cascaded (SW <-SWCD <- DGRP <- EQT <-DATA). The Main form SW (single view) has a subform for each table (dataview) but they are linked to the previous subform like above (not main form except SWCD). I am trying to figure out the best way to search on the EQPT field (from the EQPT table/subform) and display the correct results in the Main form with the subforms. Can any one help?
 

A:Solved: Access Find Record on Subform Field

Read other 8 answers
RELEVANCY SCORE 106

Hi all

I have a simple Access (2003) db which has a single form view with a subform. The main form is a record based upon a physical case file the subform only details actions past and future, a sort of event log.

I also have a continuous form which displays all upcoming actions sorted by date on all cases for a particular user so they can see just how busy they are likely to be for a particular period. What I would like to do is have an on click() property for the detail of the continuous form so that it opens the main form filtered by the record in the continuous form that was clicked. User can then update or add new events for that case before closing form and returning to the continuous form

Hope this makes sense
 

A:MS access open single form filtered by selected record in continuous form

coasterman, welcome to the Forum.
It makes perfect sense.
If you add a Command Button to the Continuous Form and after selecting the mainform select the "Open the Form and find specific data to display". This will give you the code that you need to add to your On Click or On Double Click property or of course leave the button and use that.
 

Read other 2 answers
RELEVANCY SCORE 102.4

Hi guys.
Just a couple of quick questions (I hope).
I have a user who enters vehicle delivery and handling details into a form in an Access db. She wants to add a new form for additional details about the same vehicles (records).
The two forms would be linked via a button on each in the header area of the form, allowing the user to quickly switch back and forth between the two.

What I need to know is, if I search for a record in form 1, and then click the button to open form 2, can I make the same record appear automatically?
Am I making any sense?
Erm...
Okay, so I've searched for record number 6 on either form, I've clicked the 'switch forms' button, I wanna see record number 6 smiling back at me.
It sounds like it's something really simple, but then again, this is Access.

Second question, do you think I should add the fields for the new form into the same table that supports the original form, or into a new related table?

Cheers.

Gram
 

A:Access 97 - go to same record in another form

Read other 8 answers
RELEVANCY SCORE 101.2

I have a form for displaying existing records and a command button set to "Add New record" which gives a clean form for entering a new record.

Nothing very clever there, but I would like to be able to arrange it so that when the new form opens, the cursor is already positioned in the first field to be completed. The field properties already show Tab Stop - Yes, Tab Index - 0, and I have tried it with Auto Tab - Yes and Auto Tab - No, but I can't get it to work.

I'm sure there must be a very simple solution to this, and I'm sure TSG knows.

Thanks for your help.
 

A:Access - New Record Form question

View>Tab Order
 

Read other 3 answers
RELEVANCY SCORE 100

Here is the situation,
I am a novice, no the less I have been given this position, I must cut a record from a form that has drop down lists, take that data and paste it into a past employee table. When I do this I get the data that was displayed in the look up tables not code. Please any help or step by step is much appreciated.
 

A:Solved: Access 2010 Cut record in form

Read other 13 answers
RELEVANCY SCORE 100

I have been put in charge of creating a database to record all of our quality audits on parts. (a simple part inspection)

Most info will be entered into the Audit form named frmAudit. However, if the parts fail inspection another form is opened from frmAudit called frmNCM.
All the info entered into the NCM form needs to be linked back to the audit form. So, my NCM table has a field for AuditID.

The problem is I can not get the NCM form to open and automatically fill in the AuditID in the NCM form from the Audit form.

My current code:

Private Sub btnNCM_Click()
On Error GoTo Err_btnNCM_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmNCM"

stLinkCriteria = "[AuditID]=" & Me.[AuditID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_btnNCM_Click:
Exit Sub
Err_btnNCM_Click:
MsgBox Err.Description
Resume Exit_btnNCM_Click

End Sub
-------------------

Thanks for the help
 

A:MS Access Open form new record ID field

Read other 6 answers
RELEVANCY SCORE 100

I've looked around the net, but haven't been able to find a decent answer.. Can anyone tell me if its possible to export a single record in form view as an image? either jpg or pdf..

I've got a database that has text placed over the top of a background image and would like to be able to save/export different records including all their data as an image file.

Im using Access 2003.
 

A:Exporting an Access record (form) to an image

In one word NO! not automagically
But you can use a photo editing tool to take screen shots or snapshots & use them
 

Read other 3 answers
RELEVANCY SCORE 100

I need to duplicate a record on my main table by using a form off the main table. I know you can simply use the wizard with a command button but I don't want to use the wizard. I need to write a macro for the command button because I want the macro to do other things like add a message and so on. I have tried the setvalue option but it didn't seem to work. I want to duplicate the entire record into a new record. My primary key is based on an auto number so that should be the only thing that changes. Do you know how to write a macro to duplicate a record in a table?
 

A:Access. How do I duplicate a record on a form using a macro.

I don't have instructions for doing it with a macro but if you modify this and put it on the On_Exit event of the last control on your form the values will be carried over to the new record by default.

With CodeContextObject

.YourFirstField.DefaultValue = """" & .YourFirstField & """"
.YourSecondField.DefaultValue = """" & .YourSecondField & """"

End With

Just replace the YourFirstField, YourSecondFiled with the names you are using and it should work for you. If you insist on doing it with a macro I think you have to use the set value like this:

Action > SetValue
Item > [Forms]![FormName]![YourFirstField]
Expression > """" & [YourFirstField] & """"

I think that is how you do it with a macro. Let me know if you need more help.

[Edited by Talismanic on 06-06-2001 at 09:58 PM]
 

Read other 3 answers
RELEVANCY SCORE 99.6

I want to use the GoToRecord command to go to a specific record in a form. The problem is this uses an offset or record number. How can i find the record number of a specific record?

What i plan to do is use some code similar to this:

docmd.GoToRecord acDataForm, "FrmComputer", acGoTo, expression

where the expression is the record number of the record 'Computer01' for example.
 

A:Access. Find the record number of a record (For forms and queries)

Read other 13 answers
RELEVANCY SCORE 99.2

When I have a record pulled up on a form I want to have a subtable/form of additional information that is unique to that record. I want to put a button on the first form to open "frmContactInfo" I can't seem to get it to work right. I have a relationship of "tblContactInfo" to "tblProjects" so that the tables are linked. I can get it to work put when I go to a different record and click the button again it pulls up the wrong data.

How can I get it to only pull up the data asscoiated with the first record? It is probably simple but I'm just not getting it.

Thanks, Joe
 

A:Solved: Add subtable to existing form record-Access

Read other 6 answers
RELEVANCY SCORE 99.2

is there any way around this?
 

A:why isnt the my record selector working in access form?

What are the other properties for "Allow Additions"/"Deletions" set to and are you using the switchboard to open the form, if so what mode did you choose for the switchboard to open the form?..."Add" or "Edit" mode.
 

Read other 3 answers
RELEVANCY SCORE 99.2

Hi!
I'm a newbie to Access 2007, have done programming prior to retiring. Everything I read suggests that Access is EZ for data entry. but I must be doing something wrong. Have the relationships of 3 tables established, query works fine, form pulls up data that I manually input into the 3 tables, BUT I can't figure out how to add a new record using the form.

I've build various forms using only one table, multiple tables, query based on 1 table, query based on 2 tables, query based on 3 tables. All the queries work pulling up existing data. I've build forms one each of these queries as well as using the tables directly, but all the forms show me all the records in the database, but don't allow me to enter a new record. Any suggestions?

Thought this was going to be ez, but have spent hours and hours working on building a data entry form with zero to show for my effort. Any suggestions???
Thanks,
jlamare

Tech Support Guy System Info Utility version 1.0.0.1
OS Version: Microsoft Windows 7 Home Premium , Service Pack 1, 64 bit
Processor: AMD Phenom(tm) II X4 840 Processor, AMD64 Family 16 Model 5 Stepping 3
Processor Count: 4
RAM: 3839 Mb
Graphics Card: ATI Radeon HD 4250, 256 Mb
Hard Drives: C: Total - 715301 MB, Free - 689565 MB;
Motherboard: ASUSTeK Computer INC., M4A88TD-M/USB3, Rev X.0x, MF70B1G05300297
Antivirus: None
Also use System Mechanic Pro
 

A:Solved: Unable to add NEW record in Access 2007 Form

Read other 6 answers
RELEVANCY SCORE 99.2

Question:
I have a form that shows the user all their inputs for the day in a list box - they can click on a record and be presented with that input so that they may make changes and edit their input.

The coding to collect the primary key from their list works - I can show it on the new form BUT
how do I open the table so that it populates the form. Or do I have to collect all the fields in embedded SQL in VB - assign them to unbound fields in the form and when the user has finished editing, I have to use VB to update the fields.

I really hope there is an easier way to do this than the VB way.
 

A:Access 2007 Form - edit database record

Read other 11 answers
RELEVANCY SCORE 99.2

I figured out the solution. Thanks for looking.
Moderator, please delete this thread.
Have a form (transaction form) containing 8 buttons.
Each button will open another form.

Each of the 8 forms being opened are based on the same table as the original form.(Too much data in the table to be shown in 1 form. Couldn't make it work by breaking some of the data into another table and trying a 1 to 1 relationship.)

I would like to have the forms open to the same record as my original form. Right now they are all defaulting to the first record no matter what record is selected in my original form.

I would like to accomplish this in VBA code.

Thank you in advance for any help.
 

Read other answers
RELEVANCY SCORE 99.2

Hello,

Using Office 2002 (I know, but it usually does what we want it too ).

We have an Access DB including 2 forms that display data taken from 1 table (one form displays the general info, the other the account details).

I have placed a button on each form that opens the other form then closes the first form. What I am wanting it to do is open the other form at the record that was displayed on the 1st form. I don't want it to filter so you can only view the current record (example: from Form1: Record1: click the button and it will take us to Form2: Record1: but once we are finished in there we can search for other records whilst in Form2: then click to go back to Form1: displaying the last record viewed in Form2

I did a google and got a few suggestions but none seemed to work, so I am now back at the "open the other form" code.
Private Sub OpenAccounts_Click()
On Error GoTo Err_OpenAccounts_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "BCAccounts"

DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "Data"

Exit_OpenAccounts_Click:
Exit Sub

Err_OpenAccounts_Click:
MsgBox Err.Description
Resume Exit_OpenAccounts_Click

End Sub
Any suggestions?

TIA
Slimboy Fat
 

A:Solved: Access - Open Another Form at Current Record

Read other 7 answers
RELEVANCY SCORE 98.8

I have a form which has a subform called SearchMainSub with displays data from the following:

Me![SearchMainSub].Form.RecordSource = MyRecordSource
'
'If no records match criteria, display message and move
'focus to Clear button
'
If Me![SearchMainSub].Form.RecordsetClone.RecordCount = 0 Then
'MsgBox "No Maintenace Records Found", 48, "No Records Found"
Me!Clear.SetFocus
Else
'
'Enable control in detail section

The problem that I am having is that it only displays the first record. I have tired all of the options in the data field for RecordSet Type. It still only displays the first record. Anyone have any ideas? I hope this one is as easy as the other question I asked.
 

A:Solved: Access 20007 Record Display 1st record only

Read other 6 answers
RELEVANCY SCORE 98

I've made a button on a form which, when pressed, adds 1 to one field and adds 7 to another.

The problem I am having is selecting which record this applies to.

I am wanting for the button to apply to the record on display in the form.

I've been able to get the number of the current record into a variable lngrecordnum but am unsure how I can use this to select the record I want to edit

Any help is much appreciated. My code is below:

Sub CallMade()
Dim dbs As DAO.Database
Dim rstQuotes As DAO.Recordset
Dim lngrecordnum As Long
Set dbs = CurrentDb
Set rstCustomerQuotes = dbs.OpenRecordset("Quotes Table")

lngrecordnum = Forms![Quotes Table Form].CurrentRecord

'Select which record here

rstQuotes.Edit
rstQuotes!Call_attempt = rstQuotes!Call_attempt + 1
rstQuotes!Priority = rstQuotes!Priority + 7
rstQuotes.Update
End Sub
 

A:Solved: Access edit data of currently open record in a form

Read other 8 answers
RELEVANCY SCORE 98

I am trying to delay records being shown on my access database form for 30 minutes from when the data is entered.

My database is populated by emails coming in, and some need to be left for 30 minutes before they need to be dealt with.

I was hoping to be able to put a DateDiff function in the filter when opening the form so that those wouldn't be displayed, but I can't seem to get it to work.

My DateDiff function looks like this:
DateDiff("n", Received, Now())

I think this should give me the number of minutes difference between when the email was received and the time now. I could then put > 30 to make it only display those that are more than 30 minutes old.

I think the problem might be the double quotes, but am not sure.

Any help to my thoughts, or a completly different solution to mine is welcome.

Thanks
 

A:Solved: Delay showing record on Access form for 30 minutes

Read other 6 answers
RELEVANCY SCORE 98

Hi All,
I created a form that uses data from multiple tables. Recently I changed the placement of some of the data - I moved onto a new table. It resulted in my form going blank. I have spent the last few hours scouring the internet and found a few bugs, however my form is still malfunctioning!
Here's a description of what is what:
I first discovered that the form goes blank when some of the references aren't correct. (Makes sense since I changed the location of some information right?). The references where in a query accessed in the Form's 'Record Source' property. I managed to correct (I think) those, but it still didn't fix the problem.
I am an intermediate user at best so I'm not aware how the 'Query' came into play because I used the wizard to create the basic form and started adding to it. I've tried adjusting a few things and am too far to go back now!
Here's the exact situation as it stands right now:
1. My form is still blank.
2. I have a feeling since I messed around with the Record Source property that it just needs to be re-written because in the Design view, I can see errors on some of the content. The error that comes up refers to "Invalid Control Property" or " No such field in the field list".
3. My form is called New Employee Entry and my tables are Contact, Employee, Personal and Travel.
I hope that's enough to get some advice - I'd hate to have to start ... Read more

A:Solved: Access 2007 Record Source - Form gone blank

Read other 10 answers
RELEVANCY SCORE 98

MS Access 2003 crashes after I add a new record and try to edit any form field. I can add a new record in the underlying table and then go to the form and edit any field. Can't catch the error with a breakpoint either - it seems the new record is not being initialized properly from the form that creates it.

Other forms in the same database do not have this problem. No server or network or sql involved with this database.

-----------------------
(Microsoft Office Access has encountered a problem and needs to close)
AppName: msaccess.exe AppVer: 11.0.8166.0 AppStamp:46437912
ModName: msaccess.exe ModVer: 11.0.8166.0 ModStamp:46437912
fDebug: 0 Offset: 0020ba36
-----------------------
Tech Support Guy System Info Utility version 1.0.0.1
OS Version: Microsoft Windows XP Home Edition, Service Pack 3, 32 bit
Processor: Intel(R) Pentium(R) D CPU 2.80GHz, x86 Family 15 Model 4 Stepping 4
Processor Count: 2
RAM: 499 Mb
Graphics Card: Intel(R) 82945G Express Chipset Family, 128 Mb
Hard Drives: C: Total - 147644 MB, Free - 64485 MB; D: Total - 4963 MB, Free - 2775 MB;
Motherboard: Intel Corporation, D945GBI, AAC99325-202, AZBI52800690
Antivirus: avast! Antivirus, Updated: Yes, On-Demand Scanner: Enabled
 

A:MS Access 2003 crashes after I add a new record and try to edit any form field

Read other 9 answers
RELEVANCY SCORE 98

Hi friends

I find it annoying that my continuous form HAS to show the empty blank record at the bottom.
I have deliberately disabled Navigation and Record Selectors

I DO need to add new records (cmdButtonAddNewRec), but I don't want to see an empty one UNTIL I press cmdButtonAddNewRec.

Is this a behaviour I can manage through VBA?

Chris
 

A:Solved: Access 2003: Hide blank new record on form

Chris, yes you need VBA. Set the Form's Property "Allow Additions" to "No" that will remove the new record.
If when you press your Command button it doesn't take you to a new record set the Form's property back to yes in your VBA with
me.allowadditions = true
 

Read other 2 answers
RELEVANCY SCORE 98

Hi all. I'm attaching an image of a macro that came useful in many of the Modal Pop-ups that were included in Access 2007 templates.

It saves the form, then opens a form that you specify at the ID of your hyperlink. It will then requery the form and search for the records for that ID.

Is there a VB that does all this in Access, so I can get away from using the macro?
 

Read other answers
RELEVANCY SCORE 98

I need to create a button that will print a report for the data just entered into an Access Form. I was successfully able to create a button which will print the report but it prints it for all records. I just need it to print the record which was just entered. Each record does receive a unique identifier (key field). Thanks for any help..I feel like I'm just beating my head against the wall at this point.
 

A:Creating Print Button for single record in Access Form

Read other 4 answers
RELEVANCY SCORE 97.2

Okay forgive me as I NEVER want to touch databases. My wife only does little things in DB's and god bless her made a wonderful DB for us to keep track of our email accounts.

The problem is 1 button she made actually prints out ALL records to the printer instead of the one showing on the screen. THis is as far as her knowledge gets her.

Now when I look at the button in the form the name is - cmdEmailLogPrintForm

and when I look at the vb I see that it has -

Private Sub cmdEmailLogPrintForm_Click()
On Error GoTo Err_cmdEmailLogPrintForm_Click
DoCmd.PrintOut

Exit_cmdEmailLogPrintForm_Click:
Exit Sub

Err_cmdEmailLogPrintForm_Click:
MsgBox Err.Description
Resume Exit_cmdEmailLogPrintForm_Click

End Sub

Now I am fairly good at decyphering things and it seems to me the problem is with DoCmd.PrintOut

Is there any way to get this so it only prints what is currently showing in the form window NOT everything in the DB?

I have already cleaned the few other snafus I have with the DB but this one is not as minor as the others were (the others were just view layouts).
 

A:Solved: Access - Form button prints entire DB not the single record I want.

You are correct the DoCmd.PrintOut is not the correct way to print the current record.
The best method is to either save the form as a report or create a report with the same fields.
The report's data source should be a query that uses the current form record as it's criteria.
It is usual to use the form's current record's key field to "filter" the query.
This means putting in the criteria row of the query

Forms![Form Name]![key Field Name]

where form name and key field name are those used on your form.
 

Read other 3 answers
RELEVANCY SCORE 97.2

Using MS Office Access 2003/VBA with all updates current.

I want to set the recordsource of a form to the name of a query both in the form open event and and also later on while the form is open in response to changing conditions

In the form_open event, I set the form recordsource to the name of the desired query. This should cause a requery.

When I open the form I am running into a condition where one "null record" is retrieved. I.e. the form opens with all fields blank and the current record indicator and the record count indicator in the navigation controls are both set to 1.

As the form is sitting there open with this null record showing, I can explicitly open the query named in the recordsource from the database window and see that several hundred records are retrieved.

If I put a breakpoint in the open event and use the immediate window, I find that in break mode,
(1) me.recordset.recordcount returns 0
yet
(2) dcount("[Index Field]", me.recordsource) returns hundreds of records.

The situation is not improved by a desperate, liberal salting with Requery and DoEvents statements or putting the assignment in the load event instead.

Clicking a command button that invokes requery, refresh, repaint, doevents statements has no positive results.

Neither does controls that assign and reassign the recordsource property.

I've even tried using the form timer interval to give the form "time to think about it" but again, no dice.

But yo... Read more

A:MS Access 2003 null record is retrieved when form.recordsource is assigned in VB

The simple first question: is the form getting opened in Data Mode acFormAdd?

And...I can't think of anything else just at the moment. I'll post more in a bit.

chris.
 

Read other 1 answers
RELEVANCY SCORE 97.2

Hiyo.
I have a db with 2 forms, one for adding new records and one for editing existing records.
The parent forms (and underlying VesselActivityParent table) consist of an ID field, a Vessel number and an ActivityDate. The latter two fields have a combined unique index (only 1 record can exist for a particular Vessel on a given Date).

When the user tries to enter a record in the NewRecordsForm that already exists what I'd like to happen is, a msgbox appears asking if he wants to go edit that particular record. On a "No" response, it clears what he's entered, on "Yes" it closes the NewRecordsForm, and opens the EditRecordsForm at the correct record.

This is what I have so far...

Private Sub Form_Error(DataErr As Integer, Cancel As Integer)
If DataErr = 3022 Then
If MsgBox("There is already a record for this Vessel on this Date." & vbCrLf & "Go to the Edit form now?", vbQuestion + vbYesNo + vbDefaultButton1, "Duplicate record!") = vbYes Then
Cancel = True
Me.Undo
DoCmd.Close acForm, "AddRecordsForm"
DoCmd.OpenForm "EditRecordsForm", acNormal, "", "", acEdit, acNormal
Else
Cancel = True
Me.Undo
End If
Else
End If

End Sub
Click to expand...

This works fine on "No" response, and on "Yes" response it opens the Edit form at the first record.
So how do I make it go to the correct record? I don't know how to grab the ID of the record that already ... Read more

A:Access - On error 3022, open edit form at existing record

Read other 8 answers
RELEVANCY SCORE 96.8

How can i capture the record number of a record into a text box on a form?

When i talk about record number i mean the number that appears on the navigation buttons toolbar when in form view.

Thanks 4 the help.
 

A:MS Access: Captureing the Record number of a record.

Hello agian, try thei in the form's on Current event procedure to see the result -

MsgBox Me.CurrentRecord

to put it in a text box use

me.fieldname = Me.CurrentRecord

where fieldname is the name of your text box
 

Read other 2 answers
RELEVANCY SCORE 96

I am using Access 2007.
I have a form with a subform in it.

Form Name: FamilyNameForm
Subform Name: PatientNameForm

I would like to create a command button to open up another form called PolicyholderForm.

The PolicyholderForm is linked to the PatientNameForm by a field name PatientNameID>

I would like the PolicyholderForm to show the data about the individual currently displayed in the PatientNameForm. And if there is no record in the PolicyholderForm that matches, I would like to be able to enter data.

I've tried a number of things and can't seem to quite get it.

Any ideas? Thanks!
 

A:Access 2007 Command Button to open Form based on Current Record

Has Access 2007 got a Command Button Wizard, if so when creating your button do NOT choose the first Default Option, which is show all records.
Select instead the "Specific Record" version.
 

Read other 3 answers
RELEVANCY SCORE 96

Hi! I'm new to the forum, and not as knowledgable about Access 2007 as I thought I was when I started this project, so please bear with me as I trip over words and concepts!

I have a master form set up with synch'd combo boxes. The 2nd combo box displays results in a continuous form - showing a shortened version (query'd) of the matching records.

I need to be able to select a record in the continuous form and view +/ print the chosen record from the main table.

I created a report to display the record.
I created a command button to open/view the report but it opens to the 1st record in the entire database instead of the record selected in the continuous form.

How do I get the command button to open up the full record indicated by choosing the shortened version in the continuous form?

Hope this makes sense. Any suggestions / thoughts are welcome! Thanks in advance.
 

A:Solved: Access 2007 - Print single record selected on continuous form

MuddyTurtle, welcome to the Forum.
Your question makes complete sense, the easiest way to achieve what you want to create a Query for your report.
In the Key field enter this in the First Criteria Row.
forms![Formname]![Subformname]![Key Field Name]
where Formname is the name of your mainform, Subformname is the name of the subform as the mainform sees it and
Key Field Name is the name of the key field on the subform that identifies the Record that you are on.
 

Read other 2 answers
RELEVANCY SCORE 93.6

Hi –
My problem in Access 2007 is with the Openform command button; executed from a Subform’s subform. Ie. Command button is nested on third level. [RecommendationID]=[Forms]![x01aTRACKING]![xx00Issues]![tbl_Recommendation subform]![ID]
​The form opens fine for record/data already stored in the database. For new [tbl_Recommendation subform] data, the form which opens does not go to the corresponding [RecommendationID], instead opens a blank form. I can however enter in the desired [RecommendationID] label, to which it will populate with correct data - I’d like to avoid this step. Have tried
Requery after Openform in the OnClick event of the ‘Main form’, also tried Requery on the OnOpen event of the new form with no success. Anyone have any ideas?
 

A:Solved: Access 2007: Subform subform button to open form

Read other 16 answers
RELEVANCY SCORE 91.2

Hello, I am using Access 2000 and have created a database with a command button that opens up a report via a macro. I would like to know how to open up single reports instead of pushing a button that brings up every report, I've been able to open up reports that are based off of queries, queries that have paramenters that need to be inputted in order to bring up single unique records, but I would like to be able to just create a button that brings just that particular unique record-report off of that particular unique record on that form. Meaning that for a record that had just been inputted for John Doe on a form, I would like to know how to create a button on that form to pull up John Doe's single report without having to build a query that asks for a parameter. Is there anyway to do this?
 

A:Access-Printing a single report off of a single record from an Access database form

You will have to adapt this to your own form setup; but it's just using the Where line of the OpenReport macro action.

If on the form MyFormName you have two controls called txtFirstNm and txtLastNm, and in the query underlying the report you have two fields called FirstNm and LastNm, you would put into the Where line of the OpenReport action:

Code:
[FirstNm]=[Forms]![MyFormName]![txtFirstNm] And [LastNm]=[Forms]![MyFormName]![txtLastNm]
Hope that helps.
 

Read other 1 answers
RELEVANCY SCORE 85.6

Hi! I have created a database that contains a field with a part number. I am looking for a function that will allow me to copy that record in the form, but change the part number to add an alpha at the end. For instance, if the part number is T0-6-0107 I want to be able to copy the record and add an "A" to the end. If the part number is T0-6-0107A I want to be able to copy the record and change the "A" to a "B". Is that possible? Help!
 

A:Copying a Record in a Form

Read other 7 answers
RELEVANCY SCORE 85.6

Scenario: Single Table, One of the fields is Notes, Last entry is the only important data.

I know that I can scroll through the records and manually update this field but, I would like to do it via an "Update Form"...

How do I accomplish this task?

Thank you in advance.
 

A:Using a Form to Update a Record

Read other 6 answers
RELEVANCY SCORE 85.2

What audio software can I use to simply record *what I hear* from the computer? Record what comes through the speakers.

A:What audio software can I use to simply record *what I hear* from the computer? Record what comes through the speakers.

Audacity should do what you want.

Read other 3 answers
RELEVANCY SCORE 85.2

I have a table which consists of date, time, odometer reading as fields. i want to calculate mileage of my vehicle by having query in Microsoft access 2007 which subtracts odometer reading from previous record with current record. can i get the mileage between the customized date and time?

this should take care if the odometer is reset and starts a fresh readings. should have the provision to enter manually the fuel quantity whenever odometer is not working properly.
any suggestion to build a query will be appreciated. thanks in advance.
 

A:How to create a querry to calculate difference between prev record and current record

Read other 10 answers
RELEVANCY SCORE 84.4

Can someone here help me set up my tv and vcr to record the show Lost?
 

A:Help to set up my VCR to record shows?

Read other 6 answers
RELEVANCY SCORE 84.4

Hi, simple problem but can't solve it.

I have a delete button on frmVolunteer which opens a form called frmDelete which contains a yes and no button with some text info. For the yes button I have the folowing code:

Private Sub DeleteButtonYes_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End Sub

I want to delete the current record from frmVolunteer but it won't work.

Richard
 

A:Solved: Delete record from form via pop up

Read other 14 answers