To save query click the button on what Results

I have a problem which i hope someone will know the answer to, its probably really simple.

I have a form bound to a table which stores personal details of people, there is a primary key field called "log book number". What i'd like to do is click a button on my form which takes me to a test details screen. Before i go to this screen i will save the record but more importantly i need to have the log book number stored in another table as well called "test details".
Is there an easy way to do this? The "test details" table has other entries in it that are to be filled out on the test details form.

i had thought an append query but i cannot seem to get that to work.

Hope this makes sense.

Thanks in advance

I'm very new to all of this and am very stuck, so if someone could help I would be very grateful.
I have created a form that uses a couple of queries and want to instruct access to convert the information to the auto ID numbers on clicking the save record button.
The following VBA code is not working and I'm not sure what I need to do (when I click the save record button it states 'runtime error 3265' 'Item not found in this collection' and when I look at the VBA it highlights the red text below):

Dim hoteldb As Database
Dim hotelrec As Recordset
Dim hotelstr As String
Dim hotelidis As String
hotelstr = "SELECT * FROM [Hotel Name Address Query]"
Set hoteldb = CurrentDb()
Set hotelrec = hoteldb.OpenRecordset(hotelstr)
Do Until hotelrec.EOF
If hotelrec!Hotel_Name_Address_Formula = HotelName Then hotelidis = hotelrec!Hotel_ID

Dim bookdb As Database
Dim bookrec As Recordset
Dim bookstr As String
Dim bookidis As String
bookstr = "SELECT * FROM [Client Company Query]"
Set bookdb = CurrentDb()
Set bookrec = bookdb.OpenRecordset(bookstr)
Do Until bookrec.EOF
If bookrec!Client_Company_Formula = ClientCompany Then bookidis = bookrec!Client_ID

Dim db As Database
Dim rec As Recordset
Dim strname As String
Dim nonights As String
nonights = Departure - Arrival
strname = "SELECT * FROM [Reservation Details]"
Set db = CurrentDb()
Set rec = db.OpenRecordset(strname)

HCC_Reservation_ID = rec!HCC_Reservation_ID
rec!Arrival = Arrival
rec!Departure = Departure
rec!Nights = nonights
rec!Hotel_ID = hotelidis
rec!Client_ID = bookidis
rec!No_of_Rooms = No_of_Rooms
rec!Room_Type = Room_Type
rec!Amended_Date = Amended_Date
rec!Hotel_Contact = Hotel_Contact
rec!Original_Date = Original_Date
rec!Booking_Fee_Added = Booking_Fee_Added
rec!Commissionable = Commissionable
rec!Client_Rate = Client_Rate
rec!HCC_Rate = HCC_Rate
rec!Room_Rate_Includes = Room_Rate_Includes
rec!Commission = Commission
rec!Guest_Names = Guest_Names
rec!Guaranteed = Guaranteed
rec!Client_Cancellation = Client_Cancellation
rec!Client_Payment_Method = Client_Payment_Method
rec!Credit_Card_Type = Credit_Card_Type
rec!Card_Number = Card_Number
rec!Expiry_Date = Expiry_Date
rec!Cardholder = Cardholder
rec!Security_Number = Security_Number
rec!Client_Notes = Client_Notes
rec!Hotel_Notes = Hotel_Notes
rec!HCC_Contact = HCC_Contact
rec!Smoking = Smoking
rec!Executive = Executive
rec!No_of_Occupants = No_of_Occupants
rec!Reservation_Numbers = Reservation_Numbers
rec!HCC_Guarantee_Method = HCC_Guarantee_Method
rec!Client_Guarantee_Method = Client_Guarantee_Method
rec!Hotel_Cancellation = Hotel_Cancellation
rec!HCC_Payment_Method = HCC_Payment_Method
rec!Start_Date = Start_Date
rec!Issue_Number = Issue_Number
rec!Client_Purchase_OrderRef = Client_Purchase_OrderRef
rec!Amended_Time = Amended_Time
rec!Amended_CXLD_By_HCC = Amended_CXLD_By_HCC
rec!Amended_CXLD_By_Client = Amended_CXLD_By_Client
rec!Amended_CXLD_With_Hotel = Amended_CXLD_With_Hotel
rec!Booking_Cancelled = Booking_Cancelled
rec!Date_Cancelled = Date_Cancelled
rec!Time_Cancelled = Time_Cancelled
rec!Cancellation_Client_Notes = Cancellation_Client_Notes
rec!Cancellation_Hotel_Notes = Cancellation_Hotel_Notes


End Sub

Private Sub SAVE_Reservation_Click()
End Sub

Can anyone see anything obvious that I need to do or am I barking up the wrong tree and need to look elswhere within my database?

Many thanks in advance.


I'm not sure if this is a dumb question or not and I hope someone can help, but is it possible for me to have a button on a form that will open the Query Builder?

I have a form that has 3 buttons on it - 1 to access records, 1 to access reports and another which I would like to open the Query Builder when clicked. The database will be used by beginners so I've hidden the design view button and also the database window which appears at startup. All that appears when I open my database is the 3-button form which is what I want.

I thought maybe I could save a blank query and then create a macro to open that whenever the button was clicked and stipulate in the macro to change the view to design view but that didn't work.

Thanks to any help or advice anyone can give and sorry if it turns out to be a really stupid simple solution I've missed (I'm good at that!)

Hello, I’m hoping this is simple and someone could point me in the right direction….
What I’d like to do is have a button (on a form) that the user can click and run the following steps:

on click:
1. Save record current record
2. pull value from two fields: PolicyNum (string value, not a PK field) & PolYear (number, integer)
3. run query (make table) based on values from step #2 above
4. open form based off table created in step#3 above

The step 2 to run 3 is what stumps me. The rest I figure can be done in a macro, but likely this whole process should be in VBA, which I know so little about.
Many thanks in advance!!

I was wondering if any one could help with the following:

I have an access report that is populated from a query I set up.

The form I created that populates the record has a command button. When you click the button the record you are updating opens outlook and places the (record) report in the body of an email. What I was looking to do next was to add a link in the report that when clicked on would open the database and record number for the email recepient to update. The code that opens outlook and places the report in the body of the email is as follows:

Private Sub Command135_Click()
Dim strline, strHTML
Dim OL As Outlook.Application
Dim MyItem As Outlook.MailItem
Set OL = New Outlook.Application
Set MyItem = Outlook.Application.CreateItem(olMailItem)

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

stDocName = "ReportEmailNoticeofAssigned"
stFilename = "O:QualityCAPAEmailReportsmyreport.html"
strWhere = "[Improvement No] = " & Me.[Improvement No]

DoCmd.OpenReport "ReportEmailNoticeofAssigned", acViewPreview, , strWhere
DoCmd.OutputTo acOutputReport, stDocName, acFormatHTML, stFilename
DoCmd.Close acReport, stDocName

Open "O:QualityCAPAEmailReportsmyreport.html" For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Close 1
' If OL2002 set the BodyFormat
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
MyItem.HTMLBody = strHTML
End Sub

Is this possible.

Thanks for any help.

I created a database on my personal laptop that has Access 2010. There are several reports that are generated via a parameter query and with the use of macros, they are then emailed in PDF format when I click a button on the main form I use. However, the version of Access I have at work is 2003 so these buttons no longer work. I created new buttons to preview the report so that I can save as a PDF and email to those who require them. There are a lot more steps doing it this way and I know there has to be a better way.

Below is the VBA code generated by Access.

	Private Sub ReportCS_Click()
On Error GoTo Err_ReportCS_Click

    Dim stDocName As String

    stDocName = "rpt_LTL Shipments for CS"
    DoCmd.OpenReport stDocName, acPreview

    Exit Sub

    MsgBox Err.Description
    Resume Exit_ReportCS_Click
End Sub

This is a shipping report and when I click the button, I enter the date in as "1/27" and the report shows the shipping for that day. Now I need to right click and print to a PDF format and the name always default to the name of the report in Access. What I would like it to do is always save in the same folder on my hard drive and rename it to "CS - Shipping Report 2012.01.27.pdf" whereas the date would change to the one entered in the parameter.

Is this something that would be easy to code? Or can anyone suggest other options?

Hopefully I explained this well enough.

I got a bit of a problem with the search function using queries. Now I have a unbound form to display a query results, and a unbound text box where you enter the text in to search - this auto updates the query of the results - the working sample was posted by a user here ref "cool serach tool" - and the code:

Quote: Option Compare Database
Option Explicit

Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt

Me.Search = ""
Me.Search2 = ""

Exit Sub

MsgBox Err.Description
Resume Exit_ClearIt_Click

End Sub

Private Sub QuickSearch_AfterUpdate()

Me.RecordsetClone.FindFirst "[Name] = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If

End Sub

Private Sub Search_Change()
Dim vSearchString As String

vSearchString = Search.Text
Search2.Value = vSearchString

End Sub But here is the problem, I have tweaked it to queries all current live records using the yes/no funtion by adding True in the critrea of new Coulum that extracts only the up the current "live data" that has yes on it, When i type in the text field the code I am looking at its returning the correct result in the unbound form, but when I click on it to show the results in th form it is showing the information for the delisted ingredient instead of the current live ingredent!! - both sharing the same ingredient code, but different ID codes. The Primary key (ID Code) is done by autonumber - what is going wrong and how do I stop it?

My Coding on the form:

Quote: Public Sub SendMail()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer

strSubject = "Latest Job Outcomes"
strEmailAddress = "[Mail Addresses Go Here]"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendMail")

intCount = DCount("[lngJobOutcome]", "[tblJobOutcomes]" _
, "[ysnSentByMailToStaff]=0")

If intCount = 0 Then
MsgBox ("You have " & intCount & " new job outcome e-mails to send.") _
, vbInformation, "System Information"
Exit Sub

Do Until rst.EOF

strEMailMsg = rst![strStudentFirstName] & " " & rst![strStudentLastName] _
& " - " & rst![strStudentNumber] & " - " & " on the " & rst![strCourse] _
& " course" & " has informed us of a new job." & Chr(10) & Chr(10) _
& "Below are the details that have been submitted by the student:" _
& Chr(10) & Chr(10) & rst![memNewJobDescription] & Chr(10) & Chr(10) _
& "Graham"

DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False

Set rst = Nothing
Set dbs = Nothing

'Run update to update the sent mail check box
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblJobOutcomes SET tblJobOutcomes.ysnSentByMailToStaff = -1"
WHERE (((tblJobOutcomes.ysnSentByMailToStaff)=0))"
DoCmd.SetWarnings True
MsgBox "All new Job Outcomes have been sent", vbInformation, "Thank You"
End If
End Sub

Option Compare Database
Option Explicit

'ghudson 11/27/2002

'For those of us not using Access XP, we have a challenge to prevent
'our users from advancing to another record if they do not use the controls
'we want them to use or to prevent them from bypassing our validation
'procedures too ensure the current record is okay to be saved.

'The trick in my form is the value in the tbProperSave text box. The user will
'not be able to advance to another record using their scrolling mouse wheel nor
'will they be able to use the Page Up or Page Down keys nor will they be able
'to use the Shift-Enter keys to save the record. The user is forced to save
'or undo the modified record using my custom save or undo buttons before
'they can advance to another record or before they can close the form.

Private Sub bQuit_Click()
On Error GoTo Err_bQuit_Click


'Prompts the user to save the current record if it needs to be saved.
If Me.Dirty Then
MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not close this form until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
'DoCmd.OpenForm "fMainMenu"
DoCmd.Close acForm, Me.Name
End If

Exit Sub

MsgBox Err.Number, Err.Description
Resume Exit_bQuit_Click

End Sub

Private Sub bSave_Click()
On Error GoTo Err_bSave_Click


If IsNull(FirstName) Then
MsgBox "All required fields must be completed before you can save a record.", vbCritical, "Invalid Save"
Exit Sub
End If

Select Case MsgBox("Do you want to save your changes to the current record?" & vbCrLf & vbLf & " Yes: Saves Changes" & vbCrLf & " No: Does NOT Save Changes" & vbCrLf & " Cancel: Reset (Undo) Changes" & vbCrLf, vbYesNoCancel + vbQuestion, "Save Current Record?")
Case vbYes: 'Save the changes
Me.tbProperSave.Value = "Yes"
DoCmd.RunCommand acCmdSaveRecord

Case vbNo: 'Do not save or undo
'Do nothing

Case vbCancel: 'Undo the changes
DoCmd.RunCommand acCmdUndo
Me.tbProperSave.Value = "No"

Case Else: 'Default case to trap any errors
'Do nothing

End Select

Exit Sub

If Err = 2046 Then 'The command or action Undo is not available now
Exit Sub
MsgBox Err.Number, Err.Description
Resume Exit_bSave_Click
End If

End Sub

Private Sub bUndo_Click()
On Error GoTo Err_bUndo_Click


'Resets the record if it has been modified by the user.
If Me.Dirty Then
DoCmd.RunCommand acCmdUndo
Me.tbProperSave.Value = "No"
MsgBox "There were no modifications made to the current record.", vbInformation, "Invalid Undo"
End If

Exit Sub

MsgBox Err.Number, Err.Description
Resume Exit_bUndo_Click

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate


If Me.tbProperSave.Value = "No" Then
MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance to another record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
Exit Sub
End If

Exit Sub

If Err = 3020 Then 'Update or CancelUpdate without AddNew or Edit
Exit Sub
MsgBox Err.Number, Err.Description
Resume Exit_Form_BeforeUpdate
End If

End Sub

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Me.tbProperSave.Value = "No"

Exit Sub

MsgBox Err.Number, Err.Description
Resume Exit_Form_Current

End Sub

Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt

Me.Search = ""
Me.Search2 = ""

Exit Sub

MsgBox Err.Description
Resume Exit_ClearIt_Click

End Sub

Private Sub QuickSearch_AfterUpdate()

Me.RecordsetClone.FindFirst "
 = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If

End Sub

Private Sub QuickSearch_Click()

End Sub

Private Sub Search_Change()
Dim vSearchString As String

vSearchString = Search.Text
Search2.Value = vSearchString

End Sub

Private Sub Search_Click()

End Sub Thanks

I'm sure the answer to this is straightforward, but I've been hunting around on Access' help, these forums and elsewhere, and can't find any examples or solutions that fit with what I need.

Basically, my user is sitting on a form, frmSearchOnline. On this form are unbound text fields for MemberName and MemberNumber, allowing the user to freely enter whatever they like into these.

On the same form is a Save button (labelled btnAddMember), which has the following currently attached to it:

	Private Sub btnAddMember_Click()
On Error GoTo Err_btnAddMember_Click
    Dim stDocName As String
    Dim strUserName As String
    strUserName = Me.LoginName 'Ignore this bit.  It's not relevant to my issues, and is used to pass a hidden username from
one form to another
    stDocName = "frmView"
    If IsNull(MemberName) Then
        MsgBox "You must enter a Name", vbExclamation, "Invalid Function"
        Exit Sub
    End If
    If IsNull(MemberNumber) Then
        MsgBox "You must enter a Number", vbExclamation, "Invalid Function"
        Exit Sub
    End If
    If Me.MemberNumber.Value = DLookup("MemberNumber", "tblMembers") Then
    Select Case MsgBox("This number exists.  Go to Record?" & vbCrLf, vbYesNo, "Record Select")
        Case vbYes:
            DoCmd.OpenForm stDocName, , , "[MemberNumber] = " & Me.MemberNumber, , acNone, strUserName
            DoCmd.Close acForm, "frmSearchOnline", acSaveN
        Case vbNo:
            Exit Sub
        Case Else:
            Exit Sub
    End Select
    End If
    Exit Sub
    MsgBox Err.Description
    Resume Exit_btnAddMember_Click
End Sub

The validation check to ensure the Name and Number boxes have been filled works just fine, as does the code for my 'Move to Record?' query for the user. It is my validation check for duplicates (the bit in red text) that's failing.

When both boxes have been filled and the user clicks the Save button, the VB should hunt through the field MemberNumber in the linked table tblMembers for a match. If it finds an exact match with whatever the user has entered into the MemberNumber txt field, the user should receive a message that the number already exists and an offer to go to the existing record.

My first problem is with my DLookup not functioning properly, but I can't see why. I could instead go into the table tblMembers and simply make the MemberNumber field unique, but that's too sloppy for my needs, plus I need to retain control over what happens if a match is found.

My second problem is in actually creating the record if the match is clean. I think I need to use the SQL INSERT statement, but am not too sure of the syntax. In addition to tblMembers I have the linked table tblLogs. When a member is added with the Save button on the form, tblMembers should get a new row added with the contents of the Name and Number fields, and tblLogs should get a new row added with the contents of the Number field.

I'd naturally prefer to get direction on how I can teach myself where I'm going wrong, rather than have somebody supply the code for me, but my attempts to get this function working so far are proving fruitless.

Can anybody please advise?

Hi Guys

Sorry if this is in the wrong forum.

I have a report (letter) that is formed from a parameter query, the idea of this letter is for attendance, so if someone has been absent for a number of days they click the button on the form, and they are prompted for a ref no for letter, and duration person was absent for.

The problem I am facing is if I do this with a macro using outputto and openreport objects it wants you to inout the criteria twice. IE for when you export to file and then again when it prints.

The letter should be saved using the reference no given as the parameter.

Whats the best way to achieve this?

Many thanks in advance



Does anyone know of a definitive guide to exporting data from a table or query, into Word 2010?

I've googled the crap out of it and the only thing I can find is mail merge...which as far as I can tell is used for creating letters and mailling lables or emails.

What I would like to do is click a command button on an invoice which would export company name, province and date, into an existing word template, save it as a new filename, and then close.

While searching for Access help through Google, I found your forum which has greatly helped me in building my database in Access. I got all answers through your forum for many of my doubts and ‘How To’s. I thank all the members for their excellent and simple way of explaining for a novice like me.

As to the Database I built, it is almost complete and working fine. I have built a switchboard with menu choices. I have distributed this to all my colleagues and they are using fine.

I have three problems still to be addressed. I have tried to protect my database tables, forms etc through the security wizard, user permissions etc. But nothing is working. I have hidden all tables, forms etc. and unclicked the ‘Display database window’ in Startup so that only the switchboard is visible. I have also changed the switchboard properties to cover the entire screen and no maximize/minimize/close button. When a user double-clicks the desktop icon the database file opens with the switchboard menu. There is an Exit button in the switchboard which the user can click to he can click the close button of the Access window. Apart from this, the user can do nothing to corrupt the tables, forms etc., so I believed. However, one colleague clicked the ‘View – Design View’ in the Access menu bar and the Switchboard became ready for modification. Is there a method to block the View – Design View’ option in the Access menu bar?

My second problem is that we want a new database file first of January every year. Is there a method to create a new DB file with the same table, form and settings from within Switchboard? (The only option I found was to copy the DB file to another location, delete all records and rename it for the new year). Is there any other procedure?

My third problem is that I designed a Crosstab Query as given in the Help menu with criteria LIKE “*” & [Enter any character to search by: ] & “*” but when I execute the query it displays all records containing the character entered. This is not what I want. My search criteria everytime changes (it is text based). So I am unable to save Query form with a specific criteria as it will execute only defined parameters. Is there a way to make Query form where any user input (i.e. any word or number) is taken for search?

Shall be grateful for reply.

For the benefit of clarity, I would suggest anyone who reads this thread to download the entity relationship model I have produced of the database in order to understand what the hell I'm talking about.

I have points to make before I present my questions:

* The database is intended to be used to log calls and emails. These are known as cases, hence the entity Case. Each case MAY have Clients (sometimes no details of the person calling are acquired, hence the Client entity being optional).

* Case_Reference is a reference table to link unique Cases with unique Clients. It is absolutely necessary in order to keep Clients as seperate entities from their Cases.

* You'll note that the Client entity is built up using many other optional entities. These entities could otherwise be fields of the Client entity itself, but I have made a design decision.

My difficulty comes when designing the forms for this database. I have full referential integrity, the tables are fully normalised as reasonably as possible. I have ruthlessly prevented any null entries of any sort.

However, after much reading up (internet and this book I have called Access 2003 Bible), I'm still unsure as to the power and capabilities of Access of achieving EXACTLY what I think is best for the client when it comes to user interface issues. Here is my difficulty:

Noting that Client has many optional related tables, and that there are many levels of optionality in my design: when designing forms, I'm finding it tricky.

Obviously, I would like my clients to be able to enter their data, check it over, take their time, then save the data, but only once they have added everything they want to a "case". The problem is referential integrity, and the user interface.

I'm aware of subforms, but the nature of forms is quite a thorn. I want to construct a form so that the data can be entered onto it and only when a "SAVE ALL" button is clicked does the data on screen transfer onto the tables IN THE ORDER I WANT. Not one table at a time.

* I also want to determine the Primary Key value that is used for each and every entry. My database does not use autonumber fields: they are unreliable and prone to....horrible things. Thus, all my tables have a unique NUMBER type primary key. How would I go about programming some sort of "counter" per table: I'm assuming I have to make the application check the largest number (i.e. latest entry) and save the current case as the next number. Question is how?

* Additionally, the actual user interface I have designed seems impossible using Access forms. A typical scenario is this:

Someone calls. This is a Case. He gives his name. He is a Client. He gives someone elses name. This is also a Client. Thus this Case has 2 Clients. The data entry person opens a new Case. He adds these 2 Clients, enters the Case details and saves the Case.

My question is, can I implement a form whereby a list of the Clients currently attached to the current Case being entered (thus it doesn't actually exist yet!! Hence referential integrity issues) is shown in a list box (I reiterate, they don't exist yet, thus I can't query them from the database, because they don't exist).

What I mean is the data entry person makes a case. He chooses to add a client, enters the details for that client, saves that client, and repeats for the next. After all this, he clicks 'save case' and only then is all this data logged. What is the simplest way to do this.

* One more thing: all Cases have Topics. The Topics table I have constructed has a list of hundreds of topics, and each has a Type and a Name plus their IDs.

From a user interface POV, I intended to have to lists boxes: one of all existing Topics, and one of all assigned Topics on the current case being entered. The user would drag topics from the existing list to the Case list. The topics need to be removed from the existing list (to prevent the same topic being added twice) and appear in the Case list. Can this be implemented in a form: noting that nothing is saved until the data entry person clicks a "save the whole case and all other things that it needs" button.

Summary: How do I instantiate every Case until all data is entered related to it and in it before recording it onto the Access database using forms...if it is possible?

Clarification: By Application as a whole, I am refering to the external level of ANSI/SPARC architecture and various similar models. Additionally, don't be afraid to tell me I'll need to program this nonsense into Access if I have to!

	Dim sql As String

        sql = "INSERT INTO EquipmentLog (EquipmentID, State, User, Comment, Status, DateTime) VALUES ('" &
Me.EquipmentID.Value & "', '" & Me.cboNewState.Value & "', '" & Me.cboUser.Value & "', '" & Me.Comment.Value & "', '" &
Me.Status.Value & "', #" & Now() & "#)"
        With DoCmd
            .SetWarnings False
            .RunSQL sql
            .SetWarnings True
        End With

This is what I currently have but when I click a button on the form to save, I get a VBA error stating the INSERT INTO syntax is incorrect. I noticed if I changed my sql String to:

	sql = "INSERT INTO EquipmentLog (EquipmentID, State, User, Comment, Status) VALUES ('" & Me.EquipmentID.Value & "', '" &
Me.cboNewState.Value & "', '" & Me.cboUser.Value & "', '" & Me.Comment.Value & "', '" & Me.Status.Value & "')"

The INSERT INTO query works fine. But I need to time stamp the INSERT. The "DateTime" field name is in my Table as a "Date/Time" type. Am I missing something in my code? It seems like it should work fine but obviously it doesn't. Any help would be appreciated.

I have a form which has a multilist box and i select more than 1 items on the list then click on a button and it saves the items to a text box. Then a query is ran. The query has the text box reference in the criteria. It ends up copying 0 records. I copied and pasted the values from the text box onto the query and ran it and it worked. What am i doing wrong?

Private Sub TestMultiSelect_Click()
Dim oItem As Variant
Dim sTemp As String
Dim iCount As Integer

iCount = 0

If Me!ItemNo.ItemsSelected.Count 0 Then
For Each oItem In Me!ItemNo.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Chr(34) & Format(Me!ItemNo.ItemData(oItem), "000000000") & Chr(34)
'sTemp = sTemp & Format(Me!ItemNo.ItemData(oItem), "000000000")
'sTemp = sTemp & Me!ItemNo.ItemData(oItem)
iCount = iCount + 1
sTemp = sTemp & " or " & Chr(34) & Format(Me!ItemNo.ItemData(oItem), "000000000") & Chr(34)
'sTemp = sTemp & " or " & Format(Me!ItemNo.ItemData(oItem), "000000000")
'sTemp = sTemp & "," & Me!ItemNo.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

Me!MySelections.Value = sTemp
' MsgBox (Forms!SelectItemNumbers.MySelections)
DoCmd.OpenQuery "QryLoadDeclineDtlForm", acViewNormal, acEdit

End Sub

Most of the forms in my database are either unbound, or bound to a table or query. On all of these forms I use button controls to save a record, cancel, close the form, or add a new entry.

In one form, I have a main form and a subform (see illustration). The main form contains read-only information, while the subform contains assignment details, which can be updated. New assignments can also be added.

To be consistent with the other forms in the database, I want the user to be able to click on a button to save the changes before they are committed to the database, or to cancel the operation, etc. However, the problem I'm running into is the buttons are on the main form, but I need them to control, in most cases, the action on the subform. I have them on the main form for aesthetic reasons and to be consistent with the other forms in the database.

As an example, this is what my Save code looks like on a normal form:

	Private Sub cmdSave_Click()
    'Move the focus to the Hidden field

    Select Case MsgBox("Do you want to save your changes to the current record?" & vbCrLf & vbLf & "  Yes:         Saves
Changes" & vbCrLf & "  No:          Does NOT Save Changes" & vbCrLf & "  Cancel:    Reset (Undo) Changes" & vbCrLf,
vbYesNoCancel + vbQuestion, "Save Current Record?")
        Case vbYes: 'Save the changes
            Me.txtProperSave.Value = "Yes"
            Forms!Assignment_subform!DoCmd.RunCommand acCmdSaveRecord

        Case vbNo: 'Do not save or undo
            'Do nothing

        Case vbCancel: 'Undo the changes
            DoCmd.RunCommand acCmdUndo
            Me.txtProperSave.Value = "No"

        Case Else: 'Default case to trap any errors
            'Do nothing

    End Select
    Label78.Visible = False

    Exit Sub

    MsgBox Err.Description
    Resume Exit_cmdSave_Click
End Sub

In the above code, txtProperSave and Label78 are on the main form, but the records being affected are in the subform.

Any insight and help would be appreciated.

Ok so, since you guys know everything it seems, im looking for a few things here

First - i have a calculated field in a query which is the combination of entity name and date. it works great as an identifier for us since we can see when this entity's details are from. (and we want to keep the history). What i need is a button that will allow me to go to that record, edit the information and save it as a NEW record with a NEW date... clearly editing the information and clicking the ordinary save button will overwrite that record.

On the same token, is there a way to prevent or make a popup in which lets say we enter the same entity with the same date 2x it does not permit that duplicate c ombination in the record ID (now keep in mind this isnt the primary key - thogugh techinclally it could be but i was getting in trouble when i was tryign to "duplicate record and save the changes", thus why that button would be extremely helpful to avoid the duplicate record issue.

Hi, I've got a bit of an issue with a calculated textbox on a form. Everything is working on the form fine and the calculation does work if a user enters a number in another textbox but what I want to happen is when the form opens the textbox does its calculation based on the values in the other textboxs corresponding to each record.

Here's some history on the form.

The form is called "NightCount" and has 3 subforms. The main form holds the vendor info, the first subform holds the Order Info, and the 2nd and 3rd subform hold the order details. These are the records that have the calculation.

The main form and Order Info subform are linked via VendorID, the order info subform and 2 order detail subforms are linked via OrderID and those 2 subforms are linked together via a record source using InventoryID.

The 1st order details subform has the product name and a textbox labeled BarsGiven. BarsGiven comes from a query which is actually named "BarsLeft" in the query and is the running balance for that product type. Because this query isn't updateable I had to make another subform for the rest of the order details which need to be updated.

On the 2nd order details subform theres only one textbox and its called "BarsReturned". In the "AfterUpdate" event for this textbox I have a calculation taking place, it takes the "BarsGiven" from the 1st subform and subtracts it from whatever is put in "BarsReturned" and the answer is put in "BarsSold" which isn't on the form but in the table the rest of the info is. So yeah a calculated field being stored, I know the rules and this works, nothing duplicate being stored, etc...

The table for these subforms is called "Inventory" (I know not the best choice of names). Don't be confused by the field called "BarsGiven" its not whats on the "NightCount" form, this is for new product given that day, the one showing on the Night Count form is the running balance from the query.

Ok so that's the form broke down and now here's what's currently happening.

I attached the db to make life easier for getting help so here's what to do to see what you need to see.

First open the table "Inventory", scroll to the bottom of the table, the last 4 records are for the order I have ready. They are OrderID 10. The last field is "BarsReturned" you'll see there is no number in the field at this point. Now look at the field "BarsSold" you'll see the 4 records have a 0. All is good at this point.

Now keep the table open in the background and have the "BarsReturned" field to the far right of your screen so you can still see the blank fields.

Now in the navigation pane go to "NightCount" form and open it, when it prompts for name enter "11". Before pressing enter keep your eyes on the table in the background on the "BarsReturned" field and see what happens. You'll notice all the blanks for the order automatically change to a 0 as soon as the form opens. Great.

Now move the form over to the left so you can see move of the table and notice that the field "BarsSold" still shows 0 for all the records for that order. Now on the form enter a 0 in all the records under "BarsReturned" and watch the "BarsSold" field in the table, you'll see it start to populate. Great.

Now this is what I need it to do. Once the form opens and those 0's get thrown in the the table and form I need the calculation for all those records to happen at that time too just like if the user entered in the 0.

When you open the NightCount form and see the 0's go in the table that's when the BarsSold field should also change.

The math is located in the "AfterUpdate" event of the "BarsReturned" textbox on the form. I've tried putting that math in a lot of other places but it won't do it, I've tried requery in there as well but nothing. The closest I can get is by putting the "AfterUpdate" event for BarsReturned in the "OnEnter" event for "BarsReturned. When I do that after the form opens if I click off the first record it pops a questions about 2 people editting at the same time and prompts for a save. If I had save on the first screen and drop changes on the second prompts it will almost do the math. If you click in a record and out of it the math takes place without having to reenter that 0 so I know the 0 being loaded when the form opens will do the math it just needs to be requeried or whatever at the right time.

After you open the form don't use the post buttons on the form, I have a few because of trying different things. If you click one of the them it will change the status of the order to review which you would need to turn back to "On Road" to see the order again in the Night Count form, so don't click the buttons just close the form with the X to make life easier. Also after loading and closing the form you need to reset the values in the Inventory table. Blank out the field "BarsReturned" on the last 4 records and set "BarsSold" back to 0 for the last 4 records. If you don't and reopen the form the numbers will be different.

Well I said enough I'll make it more confusing then it is.

Oh its a success if you open the "NightCount" form and without doing anything you see in the table Inventory in the background that the "BarsSold" changed to around 30's then all is how I want it. If the "BarsSold" stays 0 when the form opens like it is now its not working.

I attached the db, its split so I've joined to be in C:Inventory Control, You need Access 2010

If anyone wants to help that would be awesome, I'll be sure to give back to the community in time.


I have built a form to enter new members and add them to the members table. The record source for the form is a query that pulls the fields from the members table (name, address etc.) that I need for each new member, where the first field value = null:


	SELECT Members.LastName, Members.FirstName, Members.Address, Members.City, Members.State, Members.ZIP, Members.MemberID,
Members.Phone, Members.CellPhone, Members.SignInSheetPosition
FROM Members
WHERE (((Members.LastName) Is Null));

(MemberID is excluded from the form, it is the unique index for each member)

My problem is on the form. I have a command button to save the entered data and it needs to clear the form so if there is another new member to enter the user can do so.

Right now I have tried many different things to get the command button to both save the data and clear the fields. Depending on what I have tried, the entered data gets saved to the member table either after closing and opening the form, or after clicking “refresh” on the Access ribbon, which will not be available after I split the database to protect the backend from inadvertent end-user changes.

I do know that after clicking the button the data is still “dirty” indicated by the pencil icon in Access. A previous issue, also with a form command button, I just had to start the sub routine with:

	DoCmd.RunCommand acCmdRefresh

I try to keep track of all the possible combinations that I try, and the results. But I am not a programmer or a coder, and I eventually get lost in all the possible combinations. Some of what I have tried includes:

	Private Sub SaveNewMember_Click()
If Form.Dirty Then Form.Dirty = False
If Not Form.NewRecord Then Form.NewRecord  -  (I think the VB editor complained about this one, tried it anyhow)
End If
End Sub

saves record if form is closed:

	DoCmd.RunCommand acCmdSaveRecord
   Form.RecordSource = "AddNewMemberQ"

saves record, doesnt clear form:

	DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec

works if close AddNewMember (form) and hit refresh in the ribbon:

	Private Sub SaveNewMember_Click()
If Me.Dirty Then Me.Dirty = False
 If Not Me.NewRecord Then
 DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub


	Private Sub SaveNewMember_Click()
DoCmd.RunCommand acCmdRefresh
If Me.Dirty Then Me.Dirty = False
Me.LastName = Null
End Sub


	Private Sub SaveNewMember_Click()
DoCmd.RunCommand acCmdRefresh
DoCmd.RunCommand acCmdSaveRecord
Me.LastName = Null
End Sub

Hope I got the duplicates out…I do realize some of the above examples are without the sub routine start and end statements, for clarification.
Some of the things I have tried come from several searches on this forum.

I use MS access 2003, so what I’m looking at doing for my database is add a duplicate form (duplicating some fields in the form) of an exiting record to a new record, but the new ID number assigned should be from a sequence (ie. unsed ID numbers) and would be automatically be inputted into the ID field (PK) of the duplicate form.

I’ve found two pieces of code online which I can’t seem to modify to suit what I require but with very limited knowledge of access I assumed these codes are very relevant or I could be wrong.

Code 1:

Private Sub btnDuplicate___Click()

Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

' Tag property to be used later by the append query.
Me.Tag = Me![OrderID]

' Add new record to end of Recordset object.
With Rst
!CustomerID = Me!CustomerID
!EmployeeID = Me!EmployeeID
!OrderDate = Me!OrderDate
!RequiredDate = Me!RequiredDate
!ShippedDate = Me!ShippedDate
!ShipVia = Me!ShipVia
!Freight = Me!Freight
!ShipName = Me!ShipName
!ShipAddress = Me!ShipAddress
!ShipCity = Me!ShipCity
!ShipRegion = Me!ShipRegion
!ShipPostalCode = Me!ShipPostalCode
!ShipCountry = Me!ShipCountry
.Update ' Save changes.
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark

' Run the Duplicate Order Details append query which selects all
' detail records that have the OrderID stored in the form's
' Tag property and appends them back to the detail table with
' the OrderID of the duplicated main form record.

DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Order Details"
DoCmd.SetWarnings True

'Requery the subform to display the newly appended records.
Me![Orders Subform].Requery

Exit Sub

MsgBox Error$
Resume Exit_btnduplicate_Click:

End Sub

The code above also takes into account an append query named “Duplicate order details”, a field column in the design view of the query is NewOrderID: CLng(Forms!Orders!OrderID) Append to “Order ID” and the OrderID column has in its criteria [Forms]![Orders].[Tag].

So the above code produces command button on the form, where if the button is clicked on an existing record, a new record and ID is created with the same fields of the existing record BUT does not satisfy the requirement of assigning a new ID from the a sequence.

Code 2:

Private Sub btn_Find_Click()
Dim sString As String
Dim sSql As String
Dim sRS As New ADODB.Recordset
Dim sConn As New ADODB.Connection
Dim X As Integer
Dim Y As Integer

Me.txt_Result = ""
sString = ""
sSql = "Select TalentID From tbl_talent_database Order by TalentID"
Set sConn = CurrentProject.Connection
sRS.Open sSql, sConn, adOpenKeyset, adLockOptimistic
If Not sRS.EOF Then
With sRS
X = 0
Do Until .EOF
Y = !TalentID
X = X + 1
If Y X Then 'chk to see if TalentID is sequential
sString = sString & X & " " 'if it is not, then record the non sequential number into the string
GoTo ChkSeq
End If
End With
End If
Me.txt_Result = sString
Set sRS = Nothing
End Sub

Code 2 creates a command button on the form so when I click the button in the text box all the unused numbers all displayed (ie. unused numbers refers to ID numbers not assigned to a record).

In the end I’m not access capable to modify or join these codes to add a duplicate form of an existing form to a new record however the new record ID number coming from a sequence.

Greatly appreciate your help.


I have searched the forums, and can't seem to find the solution to my problem.

I have a form for each customer, and a subform for jobs done for them. Each customer can have an unlimited number of individual jobs. I have designed a report which prints a job sheet for the customer, showing the relevant address info and such, which also prints the jobs sorted so the most recent job is printed at the top of the report.

Currently, I have a button that opens the jobsheet query, and another button to print the jobsheet report. To print the job sheet, a user has to run the query, go into design view, put in the customer's first and last names, save the changes, then print the job sheet report.

This is a tiny bit too cumbersome for folks to put up with for long. What I would like to do is have a button to press that would print the current customer record with all the relevant job records.
I have tried putting in some of the code that folks have recommended to others asking similar questions to mine, and I get syntax errors that I don't know how to fix. Currently my non-working button code is:

Private Sub Command79_Click()
End Sub

where CUSTID is the numeric primary key for the customer. (Yes, the report name is really spelled like that... :-| )

I have very little experience with Visual Basic, and would appreciate any light you knowledgeable folks can shed on this matter!
Thanks in advance,

Not finding an answer? Try a Google search.