Access 2003 - Get Current Record in VBA

I'm developing a small Access 2003 app for a local charity and it's going well using wizard-created screens, but I'm stuck on a couple of issues:
1. One of the fields needs to be automatically populated with the creation date of the record. How do I get to that?
2. I'd like a control to be visible or not depending on the value in one of the fields in the currently displayed record; how do I access from VBA the fields of the current record?
3. I'm using a tab control but in Form View the screen opens up scrolled down slightly so the tabs are not visible; resizing the screen in design view makes no difference to this.
Any help would be appreciated!
Brian


Post your answer or comment

comments powered by Disqus
Hi,

I'm currently designing a database.

I have a main table and a one - to many related table.

The main table (MASTERTABLE) stores details of a group.
The related table (Presenter) stores meeting records.

I'm writing this database in access 2007, but in 2003 format as the end result will be used in 2003 (irritating corporate policies!!).

I have a button to open the current record in a report. Said report (based on "MASTERTABLE" has a sub report to show the "one to many" related record for meeting dates (Presenter).

Now my current code to do this is as follows:


	Code:
	RunCommand acCmdSaveRecord
DoCmd.OpenReport "CFG Extract", acViewPreview, , "id=" & Me![ID]

This works fine in 2007, and is ok in 2003, except for one scenario:

I can open the current record providing there are no record related to it in the subreport.

when there are, access 2003 generates an error about the reference to "ID" and refuses to open the report. 2007 however happily does what it is told.

I had the feeling that the field on the subreport called "ID" (being the key field) may have messed with this, and i renamed it to ID2, only to find earlier today that this did not resolve the issue.

I have tired several "older" ways of calling the current record, all of which work in 2007. Some taken from 2003 tutorials.

any ideas on this conundrum?

I can supply more code or samples from the database if required.

Hi all,

i have a sub form which get its data from a table.
now, what i want is to do a bit of calculation after every update for each row.

my problem is, when there is more than 1 rows, my code can not see the current record.

Here is some illustration for easier understanding:

this is the table data:
box1 box2
1 1
2 1
4 2

Form -> sub form
sub form gets data from table
sub form has 2 boxes (box1 and box2)

I added a new record and type 5 in box1.

then when i try to debug.print Me.box1, i always got '1', not the new value that i typed in (5).

how do i move the cursor to the new record that i just typed ?

thank you and hopefully my question is easy to understand

Hi all,

I'm new to all of this and this is probably straight forward. I have a form in a MS Access database which is basically to be used like a checklist for a set of business processes in processing an application form that is received by the business. (The database is used to record the information from the application form and monitor stages to the application process.)

The 'checklist' is also used as a trigger point. That is, when certain items are flagged as complete, the 'status' changes automatically to let the user know that stage is complete.

I have tried putting a line of code under the "On Change" event of a combo box...

If Me.AppStatusID = 0 Then
Me.AppStatusID = 1

and that works fine for the first record. When I add a new record (next line item) using the combo box, the AppStatusID always goes back and changes the first record entry and not the current applicable record line. Could you please help? Presumably something needs to tell it to use the current record line?

Thank you

Access 2003, DblClick copy record different table - MUST NOT BE LINKED
Note - novice user.
TableA is large list of medical equipment names - displayed in Form, TableA, and List Box, AssetDescriptionA
User double-clicks, and populates TableB, Subform, Text Box, AssetDescriptionB
TableB has AutoNumber, and many fields and linked tables (from which all management is performed)
TableA, and Table B, must not be related, linked in any way - TableA's only purpose is to populate TableB.
I have searched and tried many methods and none have worked. I would really appreciate if someone can point me in the right direction.
I assume Event Procedure code is the way to go?
Thanks, Alan

I need help to get current record with different ids:
PHP Code:
Dim strDocName As String

Dim strWhere As String
    strDocName = "16kanaler"
    strWhere = "[skjema_16_gr1_ID]=" & Me!skjema_16_gr1_ID
    DoCmd.OpenReport strDocName, acViewPreview, , strWhere 
How can I use more Ids in the wherecondition? I want to get the current record for skjema_16_gr1_ID and skjema_16_gr2_ID, not only the first.

Please help me...

Håvar

I want to highlight the current record in the datasheet of a split form (Access 2007).

To that end, in the form's OnCurrent, I have put:


	Code:
	SendKeys "+ ", True

It works fine, once the user clicks on a row somewhere in the datasheet. But when the form loads, the first row in the datasheet, the current row, is not highlighted. Only if I navigate to it later in the datasheet-part.

I put the coad in the OnLoad, but that does not do it either.

Any suggestions?

Is it Possible to high-light current record in data-sheet view?

I would like to improve my form that use datsheet views; how can i make access high-light the current record (like make it yellow)? when user moves through records, i would like to highlight record to make viewing easier;

Can this highlighting be done in continous forms, too? What is the how-to?

Thank you!

--llyal

Hi Forum

I have made i small access database (2007)
now i need to make a Query whitch shows
the current record in i a form.
is there someone that could help me out.?

Greetings

B.Hansen, Denmark

is it possible to open a current record in a report, what I mean is while the user is going over the records and lets say that he wanted to print the current record I need to open and print that record in a report so it would look more professional, how can I do that?

Thanks.

Hi,
I have a table called 'Drawings register' that stores the names of technical drawings, the drawing numbers and their revision numbers.

The idea is that the user clicks on the Add new drawing button on the form, enters the drawing number in the first text box and the revision number in the second text box. Then what I would like is to check if that drawing with that revision aleady exists.

I finally got the code working so that it checks if a drawing number with a particular revision number exists but now I want to jump to that record if it does exist. Otherwise the user continues to add a new drawing.

How do I jump to a specific record in VBA? Is this possible to do in VBA or is there a better way of doing it?



	Code:
	Dim drawingNum As Variant

    'checks if drawing num and revision number entered in the form already exists in the drawings table
    
    drawingNum = DLookup("[Drawing num]", "Drawings register", "[Drawing num]=Forms!frmAddDrawings![txtDrawing] And
[Rev]=Forms!frmAddDrawings![txtRev]")
     

   If Not IsNull(drawingNum) Then
      Beep
      MsgBox "That drawing number and revision already exists", vbOKOnly, "Duplicate Value"
      Cancel = True
   End If



Hi Guys,

I'm trying to create a command button on a form that will print only the current record in 'Landscape' orientation, when required by the user.

Is this possible? If so, any ideas/suggestions of how i can achieve this please?

Thanks alot.

Kind regards,

Amber

I have a form that has a subform in which users can update a field called APPROVED_COUNT. If the count in this field does not equal another field called ROW_COUNT, I pop up a message to alert the user of the issue and if they answer YES to the message, I want to return right back to the record in question to allow them to fix the count. I've tried various things (movenext, movelast, etc) but I can't seem to figure out how I can return to the current record in the recordset.


	Code:
	Private Sub APPROVED_COUNT_AfterUpdate()

    ' when the manager has updated the APPROVED_COUNT field,
    ' we update the APPROVAL_DATE and APPROVING_MGR fields
        
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "Approved Count  DFU/SKU Count for location: " & Me.DEALER & "  " & _
            vbCrLf + vbCrLf + "Do you want to change the approved count ?"
    Style = vbYesNo + vbInformation + vbDefaultButton2
    Title = "Record Counts Don't Match"
        
    If Not IsNull(Me.APPROVED_COUNT) Then
        
        If Me.SKU_COUNT.Value = Me.APPROVED_COUNT.Value Then
            Me.OK_TO_PROCESS.Value = "Y"
            Me.APPROVAL_DATE = Now()
            Me.APPROVING_MGR = GetCurrentUserName()
        Else
            Me.OK_TO_PROCESS.Value = "N"

            Response = MsgBox(Msg, Style, Title)
            
            If Response = vbYes Then
                'Form.Recordset.MoveNext
                Me.Requery
            Else
                Exit Sub
            End If

        End If

    Else
    
        Me.APPROVAL_DATE.Value = ""
        Me.APPROVING_MGR.Value = ""
        Me.OK_TO_PROCESS.Value = "N"
    
    End If
        
End Sub



Hey everyone,

I searched to no avail.

I have a query that I am using to input data to a table. I need to display only the current record in the subform, not all the records.

Is there code that will do such a thing?

I have 3 fields in the subform being populated from combo boxes and need to display only the record of the last entries from the combos.

Thanks guys

I have a subform whose record source is a rather complex unbound query.
On the main form is a text box where I want to load a field value from the current record in the subform query.

Currently I have a button with the code:
Me!SubformName.Form.CurrentRecord

This returns the current record line number so I must be correctly referring to the form.

But how do I refer to the value of a field in the Current record?

Thanks

hi
i have 3 queries brought together in 1 form. i do not want a report to be printed out just the current record in form view. this is because i have a number of images per record.
if i print out the current record and it only has 1 photo in a sub-form attached to the main form record, then the main form will print on 1 page which is what i want [screenshot 1]. But if there are 2 or more photos attached to the main record then the print command button prints out 2 pages for 2 photos, 3 pages for 3 photos and so on [screenshot 2].
any ideas as to how to limit the print command button to print out only 1 page per record regardless of how many photos their are attached to each record??
thanks

Hello again!
Number of current record in Text Box or Label (SAME NUMBER AS ON NAVIGATION PANEL)?
Greetings from Sarajevo,
Haris

Hi,

I am looking to be able to use a button in my form to open a report based on the current record in the form.

I have looked around and seen many responses for similar questions however these are based on just a single form/report. Whereas in my scenario, I have subforms and subreports all involved.

The way in which the button works with only one report is for example:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " & [RecordID]

Basically, I need to be able to make the ID on the subreports equal the current ID on the form. So instead of having just one report equal to the record ID, I need;

[subreport1].[recordID]=[recordID]
[subreport2].[recordID]=[recordID]
[subreport3].[recordID]=[recordID]
[subreport4].[recordID]=[recordID]

Really, I need some help being able to incorporate this into the code.

Many thanks in advance,
Kyle

Hi Folks,

Wondering if someone can help me with the necessary structure and/or keywords to get this rolling. Assume I have a base table in access, the table is sorted in a particular fashion so that logically you can step thru the records comparing the current record with the previous record to determine what you need to. How would I create a vba module that will load this base table, step thru the records in the sorted method comparing the current record with the previous so that if a certain criteria is found/met it records that in either another table or can append/update a field in the existing table? For example: if I have a 1000 row table in access named [1000RowTable] that contains a normalized representation of UserID, ServiceDate, ServiceProcedure and that table is sorted in ascending order for those fields ... I would want to load that table into vba and have vba go thru the sorted table row by row comparing the current UserID with the previous UserID, if the Current UserID matches the Previous UserID then perhaps I would want a 'Yes' in a new table along with the fields that were used in the compare and if 'No' then perhaps execute some other code. Any help in terms of formatting or VBA keywords/actions that I would need to use would be awesome. Obviously when the last record, I would want to properly close out of the recordset. I am just trying to figure out how to load a recordset in terms of an access table and how to hold a record whilst comparing it to a previous record in a sorted dataset and executing code if certain criteria match or not and then closing up afterwards. I have cheated and used excel to help with a bunch of stuff when I needed to compare current with previous but would like to learn how to do something similar with VBA.

Thank you,

Joe

I am writing an application in MS Access.

I need to extract data from an Oracle database and load the results into a temporary table in MS Access so that it can be processed in VBA code.

The data being extracted takes the form of:

Code Job Type User_ID Dstamp
Job Start PICK STWMITCHELLB 2011-08-05 13:08:01
Job End PICK STWMITCHELLB 2011-08-05 13:08:36
Job Start PICK STWMITCHELLB 2011-08-05 13:08:17
Job End PICK STWMITCHELLB 2011-08-05 13:08:02
Job Start BREAK STWMITCHELLB 2011-08-05 13:08:47
Job End BREAK STWMITCHELLB 2011-08-05 13:08:46
Job Start PICK STWMITCHELLB 2011-08-05 13:08:56
Job End PICK STWMITCHELLB 2011-08-05 13:08:04


I need to get the data into a temporary table because the data is saved with starting times and ending times in different records and I need to go through each record to match a Job Start with the next Job End for a Job Type for a User.

So for instance in the table above I would start at record 1 and get Job Start for Job Type = Pick for User ID STWMITCHELLB, then go to record 2 and because it was Job End and matched the Job Type and User ID from record 1 I would take record 1’s timestamp away from record 2 and that would give me a duration for that User on that Job.

This table exists in out Oracle database. I need to create temporary table in MS Access so I can work on the results.

The VBA I currently have is this:


	Code:
	Private Sub bt_Go_Click()
  Dim msg As Variant

  Dim sSQL As String
  Dim sConn As String, sServer As String
  Dim rst As ADODB.Recordset, cnn As ADODB.Connection
  Dim fld As ADODB.Field, iCol As Integer, lRow As Long
  
  Dim qDate As Date
  Dim sDateFrom, sDateTo As String
    
  On Error GoTo Err_bt_Go_Click
  
  ' Connect to Database
  Set cnn = New ADODB.Connection

  sServer = "some server ID"

  cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
    "Server=" & sServer & ";" & "Uid=Username;" & "Pwd=Password"

  Set rst = New ADODB.Recordset
  
  qDate = Me.cal_Shift.Value ' Get Date from Shift Calendar
  
  ' Convert Dates to SQL String
  sDateFrom = Format(Day(qDate), "00") & "-" & _
  Format(Month(qDate), "00") & "-" & _
  Format(Year(qDate), "0000")
  
  sDateTo = Format(Day(qDate), "00") & "-" & _
  Format(Month(qDate), "00") & "-" & _
  Format(Year(qDate), "0000")
  
  msg = MsgBox(sDateFrom & " to " & sDateTo, vbOKOnly, "Msg")
  
  sSQL = " SELECT it.CODE, it.JOB_ID, it.USER_ID, TO_CHAR(it.DSTAMP, 'YYYY-MM-DD HH24:MM:SS')"
  sSQL = sSQL & " FROM INVENTORY_TRANSACTION it"
  sSQL = sSQL & " WHERE it.CODE = 'Job Start' OR it.CODE = 'Job End' AND it.DSTAMP >= TO_DATE('" & _
  sDateFrom & " 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND it.DSTAMP

Hi,

How to assign Date variable into existing recordset variable and the same date variable should be assigned to [START_DATE] label in forms, it should update date in forms while we add new data . How to resolve using VBA code in MS Access 2003.
Please see the below VBA code for your information.

Private Sub Combo35_AfterUpdate()
' Find the record that matches the control.

Dim RS As Object
Dim sMyVar As Date

Set RS = Me.Recordset.Clone
'MsgBox ("RecordCount: " & RS.RecordCount)

RS.FindFirst "[URL_ID] = '" & Me![Combo35] & "'"

sMyVar = RS![START_DATE]
MsgBox (sMyVar)
'RS.FindFirst "[START_DATE] = Null"
'Set RS![START_DATE] = Now()

'MsgBox ("Before Now " & sMyVar)
' sMyVar = Date
'MsgBox ("After Now " & sMyVar)
'If sMyVar Is Null Then RS.[START_DATE] = Now()
'If IsNull(START_DATE) Then MsgBox (" Start date is null")

If Not RS.EOF Then Me.Bookmark = RS.Bookmark

End Sub

Please help me ASAP

I searched the archive and didn't find quite what I was looking for, so..

I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.

I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.

Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!

A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.

I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.

Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject

I have an Access 2003 application that runs on various machines in my workgroup. The form has a "banner" image on the top that is linked remotely from a folder on the network. There is VBA code that changes the path of the banner image based on the current date.

This has run perfectly fine for the last few years, until they installed Project 2007 on three of our machines. On these three machines, the banner image is now zoomed in very far and extremely pixelated. On the machines that did NOT get Project 2007 installed, the image still looks normal.

Does anyone know what in the world could have caused this? Better yet, do you have any ideas on how I could go about fixing this? I have tried "repairing" my Access 2003 install but that didn't seem to work. This whole thing is getting very annoying and I've become obsessed with figuring out how to fix it.

Thanks

I am trying to email only the current record from my form in a report but each time, the entire report is sent. How do I code this and where do I place the code? I am a new one in access and I really need help on this. It is a simple form to be emailed and I can't get anyone to answer what seems to be a simple question. Please help !!

Forgive me, I'm new to Access.

I have a table called Tbl_Prospects and two forms, Frm_View and Frm_Marketing. Currently I have a button on Frm_View called NewProspectButton, and when I click it, a new record gets added to Tbl_Prospects and Frm_Marketing opens.

After this new record gets added, I want to modify the record in VBA. For instance I have a field named Creation_Date, (but this will NOT be a control on either form.) How would I program this? I'd imagine it being something like

Private Sub NewProspectButton_Click()
'Open Marketing Form
DoCmd.OpenForm "Frm_Marketing"

'Add new record
DoCmd.GoToRecord , , acNewRec

'Set creation date to today
currentRecord.creation_date = today 'this is where I'm totally lost

'Set person's name to Bob
currentRecord.marketingName = "Bob" 'Again, lost.

end sub

Details please, as I'm a VBA noob. Thanks a bunch!


Not finding an answer? Try a Google search.