Vba to select current record Results

All

I have written code, which places data into an XML file, using an HTML template for styling:
Public Sub generatexml3()
' declare variables
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim strOuts As String
Dim fs As Object
Dim xmlFile As Object

'create and execute the query

strSQL = "SELECT * FROM Q_email;"
Set rs = CurrentDb.OpenRecordset(strSQL)

'build a basic xml document
strOut = ""
strOut = ""
strOut = strOut & "" & vbCrLf
Do Until rs.EOF
strOut = strOut & getTab(1) & "" & vbCrLf
strOut = strOut & getTab(2) & "" _
& rs.Fields("fault_ref") & "" & vbCrLf
strOut = strOut & getTab(3) & "" _
& rs.Fields("priority") & "" & vbCrLf
strOut = strOut & getTab(4) & "" _
& rs.Fields("issue") & "" & vbCrLf
strOut = strOut & getTab(5) & "" _
& rs.Fields("business_impact") & "" & vbCrLf
strOut = strOut & getTab(6) & "" _
& rs.Fields("date/time_raised") & "" & vbCrLf
strOut = strOut & getTab(7) & "" _
& rs.Fields("raised_by") & "" & vbCrLf
strOut = strOut & getTab(8) & "" _
& rs.Fields("site(s)_affected") & "" & vbCrLf
strOut = strOut & getTab(9) & "" _
& rs.Fields("third_party") & "" & vbCrLf
strOut = strOut & getTab(10) & "" _
& rs.Fields("update") & "" & vbCrLf
strOut = strOut & getTab(11) & "" _
& rs.Fields("date/time") & "" & vbCrLf
strOut = strOut & getTab(1) & "" & vbCrLf
rs.MoveNext
Loop

'clean up by shutting down the recordset
rs.Close
Set rs = Nothing
' add closing xml tag
strOut = strOut & "" & vbCrLf
' write out the xml document
Set fs = CreateObject("Scripting.FileSystemObject")

Set xmlFile = fs.CreateTextFile("J:my datagenesys commsfault_ref.xml", True)

xmlFile.Write (strOut)
xmlFile.Close

End Sub


Unfortunately, if a new fault is added and the "email" button is clicked - the code runs and pastes every fault into the XML file.

In other words, this only works properly if the faults table is empty.

How do I tell VBA to select the current record only from the form? I tried changing each line from strOut = strOut & getTab(7) & "" _
& rs.Fields("raised_by") & "" & vbCrLf to

strOut = strOut & getTab(7) & "" _
& rs.Fields(Forms![tbl_faults].[raised_by]) & "" & vbCrLf

But this tells me that "tbl_faults" is not associated with any macro or vba code

Any ideas?

Thanks a lot,

Richard

Hi

I'd appreciate some advice on how to do this without resorting to VBA.

Simply, I want to output the current record displayed on a form to Excel. I thought of using SetValue to change a FLAG 'field' to 'Yes' and then using a query to pull up the record with this value set. Can't do it though.

I could run a Select query but don't know how to filter it to the current record.

Likewise I need a button to import an excel file into the database. If the record is new I could use an append query but if the record already exists on the database but has some updated fields how do I update the record? I need one command to do both, rather than select different imports depending on the data.

Thank you very much in advance.

I have a main form which has a continuous form (subform) on it. Once field from the selected record in the continuous form sets the SQl statement for another subform. This code works great, except that sometimes I get a "no current record" error when the main form refreshes. Could be a bug with the continuous forms? Even though the arrow is on a record, it isn't really selected. Any way to select a record with VBA?

Hi Folks,

I haven't been on very much recently due to being moved to a new position at work, I hope ya'll are well.

To the point.

I have a form where in the before update event I have the following code (ByPass is declared public to the form at the top of the vba window as an Integer):


	Code:
	Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim msgResponse As VbMsgBoxResult

10       On Error GoTo Form_BeforeUpdate_Error

20    If Me.NewRecord = False Then
30      Resume Next
40    Else
50      If Len(DLookup("InvoiceNumber", _
    "tblInvoices", "InvoiceNumber =" & Chr(34) & _
    Me.InvoiceNumber & Chr(34))) > 0 Then
60      msgResponse = MsgBox( _
    "This Invoice has already been entered." & vbCrLf _
    & _
    "Click 'OK' to go to the invoice (Check if you are duplicating)" _
    & vbCrLf & _
    "Click 'Cancel' to return to this record to change the invoice number if you mistyped it.", _
    vbOKCancel)
70          Select Case msgResponse
            Case 1
            
80    ByPass = 2
90    Cancel = True
110         Case 2
120   ByPass = 1
130   Cancel = True
140         End Select
150     Else
160         Cancel = True
170     End If
180   End If
 
Form_BeforeUpdate_Exit:
190   Exit Sub
Form_BeforeUpdate_Error:
200       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document
Form_frmEnterInvoice on line " & Erl
210       GoTo Form_BeforeUpdate_Exit
End Sub

The form updates when a save button is clicked. My goal is that it will check to see if this invoice has already been entered then give the user two options, one - go to that invoice to see if it's the one they are entering, two- cancel the save, to check and make sure they didn't mistype their invoice number.

Here is the code behind my save button:


	Code:
	Private Sub btnSaveInvoice_Click()
10    On Error GoTo btnSaveInvoice_Click_Error
    Dim ctl As Control
    Dim cnt As Integer
    Dim IntgotoRecord As Integer
20        If Me.Dirty = False Then GoTo btnSaveInvoice_Click_Exit
30    cnt = 0
40    For Each ctl In Me.Controls
50      If ctl.Tag = "Highlight" Then
60          If Not Len(Nz(ctl.Value, "")) > 0 Then
70
80              ctl.BackColor = fRed()
90              ctl.ForeColor = fWhite()
100             cnt = cnt + 1
110         End If
120     End If
130   Next ctl
140   If cnt > 0 Then
150     MsgBox _
                "The Highlighted fields must be filled in"
160   Else
170     If Me.Dirty = True Then DoCmd.RunCommand acCmdSaveRecord
180     If ByPass = 1 Then
190         GoTo btnSaveInvoice_Click_Exit
200     ElseIf ByPass = 2 Then
210         IntgotoRecord = _
            DLookup("pkInvoiceID", "tblInvoices", _
                    "InvoiceNumber =" & Chr(34) & Me.InvoiceNumber & _
                    Chr(34))
220         Me.Undo
230         DoCmd.gotoRecord , , , _
                             IntgotoRecord
240         Call ResetEditControls
250     End If
260     ByPass = 0
270   End If
btnSaveInvoice_Click_Exit:
280   Exit Sub
btnSaveInvoice_Click_Error:
290   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSaveInvoice_Click of VBA Document
Form_frmEnterInvoice on line " & Erl

300   GoTo btnSaveInvoice_Click_Exit
End Sub

The red portion is the portion that I am trying to get to work. I get the "No Current record error just after the vba has gone through the before update event and is moved back to the save button code. The error is on line 170.

I have done some reading and the only solution I found is to use the error handler to ignore the error, if I do that and move to the next line of code I get a different error on line 230 that is number 2105 "You can't go to the specified record".

Any help with either error would be appreciated.

Hi folks,

New poster here, thou I have found answers in the forum before.

I have an Access 2003 database where I need to take the output from a number of records, format that data into a string and add it to a table for use in a report.

I have attempted the code to do this and researched the web on the error I am getting, which is a '3201 No Current record'. I thought the 'Do While Not rst.EOF' would trap this for me, but it doesn't seem to work the way I thought it did.

I only get the error when the routine reaches the end of the source table.

Here is the code:


	Code:
	Public Function RefLCNBuilder()
Dim db As Database
Set db = CurrentDb
Dim rst, rst1 As Recordset
Dim strSQL, strSQL1 As String
Dim strLCNSTART1, strLCN1, strREF, strLCNPart As String
'SOURCE DATA SET
strSQL = "SELECT QRY_reference_tasks_MPOL_unsched.REFLCNCA as REFLCN, QRY_reference_tasks_MPOL_unsched.LSACONXB AS LCN FROM
QRY_reference_tasks_MPOL_unsched ORDER BY QRY_reference_tasks_MPOL_unsched.REFLCNCA,
QRY_reference_tasks_MPOL_unsched.LSACONXB"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
'COMPILED DATA SET
strSQL1 = "SELECT [REF LCNS].LCN, [REF LCNS].[REF LCNS] AS REFLCN, [REF LCNS].[REF LCNS1] AS REFLCN1 FROM [REF LCNS]ORDER BY
[REF LCNS].LCN"
Set rst1 = db.OpenRecordset(strSQL1, dbOpenDynaset)
If ((Not rst.BOF) And (Not rst.EOF)) Then rst.MoveFirst
'ADD LCNs TOGETHER'
    Do While Not rst.EOF
       strLCNSTART1 = rst!REFLCN
       strLCN1 = rst!LCN
       strREF = rst!LCN
 
       rst.MoveNext
 
       Do While rst!REFLCN = strLCNSTART1 Or Not rst.EOF
          strLCN1 = rst!LCN
          strREF = strREF & ", " & strLCN1
 
          rst.MoveNext
       Loop
 
       rst1.AddNew
       rst1!LCN = strLCNSTART1
       rst1!REFLCN1 = strREF
       rst1.Update
    Loop
 
rst.Close
rst1.Close
Set db = Nothing
 
End Function

Can anyone take a look and advise what I am doing wrong?

I am creating a dashboard and would like to put an event in the "On Dbl Click" that will open up the details to a second form. The second form is unbound with a combo box used as a filter to a subform.

The first form is subfrmInvoiceDB with the fields built from a query:
Contract_Number- Text
Invoice_No- Text
Invoice_Date- Date

Form to open up to is frmInvoices (Unbound):
cboContractNo (Contract_Number Column 1 and Contract_Data_ID column 2) bound column is 2 with the following code:

Private Sub cboJAANo_AfterUpdate()
Dim sql As String, Fieldname As String, FieldValue As String
Fieldname = "Contract_Number="
FieldValue = cboContractNo.Value
sql = Fieldname & Chr(34) & FieldValue & Chr(34)

Forms!frmInvoices.Filter = sql
Forms!frmInvoices.FilterOn = False

End Sub

I have tried modifing the code at baldyweb but am not having any luck. I also looked at code for the OpenArgs with no luck. Am new to VBA and think I missing something easy.

Dim rs As Object
Dim lngBookmark As Long

'set a variable to the current record
lngBookmark = Me.txtEmpID
'open the new form
DoCmd.OpenForm "frmEmployeesDetail"

'take it to the selected record
Set rs = Forms!frmEmployeesDetail.RecordsetClone
rs.FindFirst "EmpID = " & lngBookmark
Forms!frmEmployeesDetail.Bookmark = rs.Bookmark

Set rs = Nothing

Thanks, Anything would be helpful. Using Access 2010

Hi all,

I am new to this forum, and I think I'm posting in the right place, but I'm not sure. If I'm not, apologies.

Anyway, I am coding a database that deals with software and computer inventory. My current issue stems from a form designed to take info of newly imaged computers. Here's what I have:

Form: frmNewComp
Fields in question:
Listbox: lstGroup
Fields: txtGroup
txtCreate
txtDelete
txtGroupNotes

Within the lstGroup listbox, I have columns GroupAcctID, GroupName, AcctCreate, AcctDelte, and Comments.

So here's the deal: What I want this section of the form to do is record what group accounts are being put onto the computer as it is imaged. The source of the group accounts is another table called tblGroupAcct; this is the table that gives the listbox its information.

The listbox is a multiselect, since it may be the case that more than one group account is put onto a single pc. Now, I am farily certain I have the code for saving the selection. What I want is this: as a group from lstGroup is selected, its information is displayed below the listbox in txtGroup, txtCreate, txtDelete, and txtGroupNotes. For the onClick event of the listbox, I want VBA to detect the list row last selected, get that row's information, and distribute it to the textboxes. Seems easy enough, but I have no idea how to do this .

Please help!

Hello,

I am new to vba and have started my own crash course of trial and error.

Currently, I am using an unbound form to house a combobox, a delete button, and a create button.

The problem I face is with the delete button. I am not sure how to go about coding it. The combobox uses a row source from a simple table. I would like to select an item in the combobox and hit the delete button to delete that record.

I suspect it'll have to be done with DAO?

Any help is appreciated. I was unable to find what I'm looking for with the search function...I did at least try that first...lol.

Hi all,

My Db has a form in which I'd like the user to be able to click a button to copy name and address details from an existing record to the current record. The idea is that the button opens a small data-collection form containing a combo, they select the source record by name (no need to review the data before updating), hit Ok, and the current record's name and address is populated.

I have the data-collection form but currently it runs a standard query which opens when you hit Ok, as per the following:


	Code:
	Private Sub btnOk_Click()

Me.Visible = False

DoCmd.OpenQuery "qryCopyAddress", acViewNormal, acEdit

DoCmd.Close acForm, "frmCopyAddress"

End Sub

I don't want to view the query results, just put them into the current record. I assume I need this event to run a select query based on the value of the combo, and then update the current record with those values. I'm afraid I'm just not familiar enough with VBA to do that!

Any advice greatly appreciated.

MCR

I have a search form, which itself is a multiple items form. I have a search field - unbound field - which is used to enter a part number to search the DB for, and the result set is returned to the same form in the multiple items area.

I have recently adjusted the forms to not display the new record row, so when the form initially opens it has absolutely no records to display. (Rightfully so.)

I have started receiving random "No current record" popup errors.

I suspect that some event is firing which I have no VBA code wired to, thus my custom error handler does not handle it and the default error message comes up.

I came across this post ( http://www.utteraccess.com/forum/Cur...d#entry1942596 ), which does successfully lead to my masking the error with the following code:


	Code:
	Private Sub Form_Error(DataErr As Integer, Response As Integer)

  Select Case DataErr
    Case 3021
      '3021 is a "No current record" error
      Response = acDataErrContinue
    Case Else
      Response = acDataErrDisplay
  End Select

End Sub

1) Is the default behavior for Access / VBA to consider firing certain events when certain things happens, and if no VBA code is wrired to the event then obviously it has no code to run for that event?

If so, then any guesses what event I should write code to so I do not have to wire this code to the Form_Error event?

2) Is there some sort of event watcher that I have not come across yet in Access / VBA that would give a port-hole view into what events are firing when even if they have no code wired to them? Sort of like a SysInternals ProcessMonitor for Access / VBA.

The following code SHOULD update, the most recently created record in a recordset ( of a subfrom ) It doesn't, it 'skips' to the wrong record and changes that ..

	Code:
	Set frm = Forms!FrmNavigationLeft!NavigationSubform.Form!NavigationSubform!sfrmReferrals.Form
        frm.Requery
        With frm.RecordsetClone
            If .RecordCount > 0 Then
                .MoveLast
                .Edit
                !Client_ID = cid
                !Referral_ID = refid
                !CalcClient_Name = n
                .Update                
            End If

Is there a way using VBA to select the MOST recent record added ( which will always be the right one in this instance ) I thought Recordset.MoveLast, would take care of this by definition. Thanks in advance.

How do you select a record based on its key? For example, I want to edit record #31 on this next action... how do I grab that record?

Partially as an addendum, how do you set the current field you are searching in? For example this line of code:

DoCmd.FindRecord Record1, acEntire, , acSearchAll, , acCurrent

will search all the records to find a record with data equal to the "Record1" variable data, and it searches in the current field. But how do you set that field for the search?

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 = ""
Me.QuickSearch.Requery
Me.QuickSearch.SetFocus

Exit_ClearIt_Click:
Exit Sub

Err_ClearIt:
MsgBox Err.Description
Resume Exit_ClearIt_Click

End Sub

Private Sub QuickSearch_AfterUpdate()

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

End Sub


Private Sub Search_Change()
Dim vSearchString As String

vSearchString = Search.Text
Search2.Value = vSearchString
Me.QuickSearch.Requery

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
Else

rst.MoveFirst
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


rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
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

Me.tbHidden.SetFocus

'Prompts the user to save the current record if it needs to be saved.
If Me.Dirty Then
Beep
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"
Else
'DoCmd.OpenForm "fMainMenu"
DoCmd.Close acForm, Me.Name
End If

Exit_bQuit_Click:
Exit Sub

Err_bQuit_Click:
MsgBox Err.Number, Err.Description
Resume Exit_bQuit_Click

End Sub

Private Sub bSave_Click()
On Error GoTo Err_bSave_Click

Me.tbHidden.SetFocus

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

Beep
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_bSave_Click:
Exit Sub

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

End Sub

Private Sub bUndo_Click()
On Error GoTo Err_bUndo_Click

Me.tbHidden.SetFocus

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

Exit_bUndo_Click:
Exit Sub

Err_bUndo_Click:
MsgBox Err.Number, Err.Description
Resume Exit_bUndo_Click

End Sub

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

Me.tbHidden.SetFocus

If Me.tbProperSave.Value = "No" Then
Beep
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"
DoCmd.CancelEvent
Exit Sub
End If

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
If Err = 3020 Then 'Update or CancelUpdate without AddNew or Edit
Exit Sub
Else
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_Form_Current:
Exit Sub

Err_Form_Current:
MsgBox Err.Number, Err.Description
Resume Exit_Form_Current

End Sub

Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt

Me.Search = ""
Me.Search2 = ""
Me.QuickSearch.Requery
Me.QuickSearch.SetFocus

Exit_ClearIt_Click:
Exit Sub

Err_ClearIt:
MsgBox Err.Description
Resume Exit_ClearIt_Click

End Sub

Private Sub QuickSearch_AfterUpdate()

DoCmd.Requery
Me.RecordsetClone.FindFirst "
 = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
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
Me.QuickSearch.Requery

End Sub

Private Sub Search_Click()

End Sub Thanks

Hi All,

I have two tables: tblLicensedPrem and tblLicensedPremHistory (these tables are identical).

tblLicensedPrem contains records for licensed premises. Over time details of a licensed premises change: e.g. the premises changes its company name, opening hours, manager, telephone number etc

What I would like to do is add a button to a form that when clicked:

- Firstly, appends the CURRENT record, in its current state, into the table tblLicensedPremHistory
- Secondly, allows editing of the current record so details can be updated (although I am not to worried about this step at the moment).

I think I am best off doing this in VBA – however I am new to this and struggling.

As a test (I’m trying to take this on one stage at a time!) I have added a button named cmdArchiveData to my form and as a starter tried to copy only the record with PremID equal to 1, and only the first three fields in tblLicensedPrem. This event is running off of the OnClick Event of the form button. However for some reason this is not working.

Can anyone tell me where I am going wrong?

Regards,

Kevin


-----CODE---

Private Sub cmdArchiveData_Click()
'Run Archive - Append to tblLicensedPremHistory

Dim db As Database
Dim strSQLAp As String

Set db = CurrentDb

strSQLAp = "INSERT INTO tblLicensedPremHistory( Prem_ID, LicNumber, PremName ) "
strSQLAp = strSQLAp & "SELECT tblLicensedPrem.Prem_ID, "
strSQLAp = strSQLAp & "tblLicensedPrem.LicNumber, "
strSQLAp = strSQLAp & "tblLicensedPrem.PremName, "
strSQLAp = strSQLAp & "FROM tblLicensedPrem "
strSQLAp = strSQLAp & "WHERE tblLicensedPrem.Prem_ID = 1;"

db.Execute strSQLAp

End Sub

Hi,

Im putting together a database at the moment which hold a lot of confidential details.. The info is submitted to the database and once used yes is selected from a drop down to confirm complete. What I would then like it to do is update a specific field(s) with a for example 4 *'s so the original data in the field no longer exists and therefore makes the record useless if anyone tries to use the data.. How can I go about doing this? I have tried using the update query function but is there anyway to set it just to update the current record within the form.. Or is it possible to do it via VBA??? I'm completely stuck here...

Thanks in advance

Tom

I need to convert this into query:
The code loops through the recordset and checks to see if the current records NDC is equal to the value of the previous record, If it is set the date to that record to 1 day less than the previous record, if it's not set it to 12/31/9999

The first date it uses (for the first record) is 01/01/1901


	Code:
	   Dim rst As Recordset
    Dim strSQL As String
    Dim tempNDC As String
    Dim tempDATE As Date
    Dim TotalCount As Double
    
    DoEvents
    strSQL = "SELECT NDC, AWPDate, AWPExp FROM AWP ORDER BY NDC, AWPDate DESC;"

    Set db = DBEngine.Workspaces(0).Databases(0)
    Set rst = db.OpenRecordset(strSQL)
    
    If rst.RecordCount = 0 Then
        MsgBox "No record found"
        Exit Function
    End If
    
    TotalCount = rst.RecordCount
    rst.MoveFirst
    tempNDC = ""
    tempDATE = #1/1/1901#
    
    Do While Not rst.EOF
        'Me.Caption = " processing....." & (Int((rst.AbsolutePosition / TotalCount) * 90) + 10)
        rst.Edit
        If tempNDC = rst("NDC") Then
            rst("AWPexp") = tempDATE - 1
        Else
            rst("AWPexp") = #12/31/9999#
        End If
        rst.Update
        tempNDC = rst("NDC")
        tempDATE = rst("AWPdate")
        rst.MoveNext
        
    Loop
        'Me.Caption = " processing.....100%"



I have searched and search and I can't find the answer to this question when I thought it would be simple. Hopefully you can put me out of my misery!

I have a access 2007 split form with a series of search boxes and a query result window. Enter info into one or more of the search boxes, click search and you get the matching results in the query window below. That works fine but I want to work with the results of the query, how can I do this?

I was looking for the user to be able to select a record and then click a command button to open that record in a form. Or in another instance select a record in the result window, have the current from close and have the primary key passed to a another form which I use to open the split form.

I just can't see how to do it but I expect there are several ways. Happy to use VBA but my experience is with Excel VBA so I'm still getting used to the objects.

Thanks in advance!

Hey folks. I'm working on a form that, when a combo box is set to a certain status (from "In Process" to "Ready"), another field in the underlying table should be populated with the current date. Now this can be done in a macro easily if you only needed to record when the combo box's selection is changed (do a SetValue and tie it to the AfterUpdate property). However, I was trying to do some VBA to do this, since it has to be a specific statement in the combo box that triggers it.

PHP Code:
Private Sub Project_Status_AfterUpdate()
If Project_Status = "Ready" Then Me![actualDate] = Date()
End If
End Sub 
For whatever reason, I get a compiling error telling me that there's an End If statement without a block If statement.

Any thoughts? Thanks.

I have a combo used to select a record from a master table using a 'select *' query as the row source. If the user cannot find a matching master record, I want to use a separate data entry form (dialog) to let them add a new master record. When the user has added the new master record, I want to update the row source for the combo as well as set the combo's control source to be the new master record. I am new to VBA and Access (currently 2003). How do I accomplish the above?

Thanks,

Doug

Hi -

I'm trying to write a query on a listbox on a form (simplified below) that the rowsource changes based on the current record selected on the form. I can't seem to get a "Me.ID" style reference to the current primary key of the form. - so the only thing i could get working is to reference forms!dates!ID, but that breaks when i create a new a New reference to the form via VBA. Is there something i'm missing? seems like it should be easy.

Thanks in advance.

Here's my [simplified] RowSource Query for the listbox:

SELECT tblTagLinks.ID

FROM tblTagsLinks

WHERE
tblTagLinks.Date_ID=[Form]![FormName]![ID]


Not finding an answer? Try a Google search.