Solved

VBA on Click select or add record

Posted on 2009-04-09
11
1,014 Views
Last Modified: 2012-05-06
Hi experts//

Hoping someone maybe able to help with this one..What I would like to do is when a user clicks a calendar the db wiill query for the selected job date from the database and if present display the data in my form else if this date is not present it will create a new record. based on the dates already held in the fields... .. I'm very new to this so please excuse any severe blunders.. thanks
Private Sub Calendar5_Click()
 

Me![JD] = Me!Calendar5.Value

Me![JD].Requery

Me![NBD] = GetBusinessDay([JD], 1, "23456", 1, "Holidays", "Holiday Dates")

Me.Refresh
 

Dim rs As DAO.Recordset

Dim sql As String

sql = "SELECT * FROM Calendar INNER JOIN [Job Spec] ON Calendar.[ID] = [Job Spec].[ID] WHERE (((Calendar.[Job Date])= " & Me![JD].Value & "));"

Set rs = CurrentDb.OpenRecordset("Calendar", dbOpenSnapshot)

 If rs.BOF And rs.EOF Then 'IT MEANS THERE ARE NO RECORDS, WE ARE AT THE BEGINNING OF FILE AND END OF FILE

 Else

   Do Until rs.EOF 'DO UNTIL END OF RECORDSET

'BELOW MIGHT NOT BE THE CONDITION YOU ARE AFTER, EXAMPLE ONLY

If rs![Job Date] = [Forms]![Main]![JD] Then 'NOTE FIELD NAMES ARE REFERENCED BY ![]

DoCmd.RunSQL sql

Else

  rs.AddNew

  rs![Job Date] = [Forms]![Main]![JD]

  rs![NextBusinessDay] = [Forms]![Main]![NBD]

  rs.Update

End If

rs.MoveNext 'THIS MOVES TO THE NEXT RECORD IN THE RECORDSET

Loop

'CLEAN UP MEMORY AT END

If Not rs Is Nothing Then

   rs.Close

   Set rs = Nothing

End If
 

End If
 

End Sub

Open in new window

0
Comment
Question by:dodyryda1
  • 6
  • 5
11 Comments
 
LVL 14

Expert Comment

by:RDWaibel
ID: 24107785
How do you plan on displaying the jab info?  On a sub form?
0
 

Author Comment

by:dodyryda1
ID: 24107837
hi RD

yep. I have a Main Form with a subform on and wish to view the results (copy my db at http://www.access-programmers.co.uk/forums/showthread.php?t=169764 )

all i wish to do is check for a date value and display corresponding record, this is not there then add a new record..

Dim rs As DAO.Recordset

Dim sql As String

sql = "SELECT * FROM Calendar INNER JOIN [Job Spec] ON Calendar.[ID] = [Job Spec].[ID] WHERE (((Calendar.[Job Date])= " & Me![JD] & "));"

Set rs = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

 If rs.EOF Then 'IT MEANS THERE ARE NO RECORDS, WE ARE AT THE BEGINNING OF FILE AND END OF FILE

 rs.AddNew

  rs![Job Date] = [Forms]![Main]![JD]

  rs![NextBusinessDay] = [Forms]![Main]![NBD]

  rs.Update

  Me.Requery

End If

Open in new window

0
 
LVL 14

Expert Comment

by:RDWaibel
ID: 24107973
very good.  I just got you db.  Let me take a look
0
 
LVL 14

Expert Comment

by:RDWaibel
ID: 24108464
First off, you are using two additional calendar controls (I do not have them)

1) CalAdapter 1.1 {C:\Program files\nokia\adapters\cal3a5.dll}
2)CalendarGFI ActiveX {D:\Program files\GFI\LANGaurd 9.0\calendargfi.ocx}

You build an SQL statement but never attach it to a "sub form".  you have the "table" as a sub form.

I would recommend you create a JobDetail subform and set it's record source to the SQL statement.

As I work with this I am finding many issues.  You have teh right idea but I think we need to rethink the process.

There are Many ways to fix this.  I would start by making the main form's recordset the Calendar table.
0
 

Author Comment

by:dodyryda1
ID: 24135563
hi rd thanks .. will give this a ago today...
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:dodyryda1
ID: 24137581
Hi RD .. thanks have done your suggestion and moved subform recordset to query results... have amende my code but bombing out now with a no current record error when i hit rs.movenext,, any ideas ?
Private Sub Calendar5_Click()

Me.Dirty = False

Me![JD] = Me!Calendar5.Value

Me![JD].Requery

Me![NBD] = GetBusinessDay([JD], 1, "23456", 1, "Holidays", "Holiday Dates")

Me.Refresh
 

Dim rs As DAO.Recordset

Dim sql As String

sql = "SELECT * FROM Calendar INNER JOIN [Job Spec] ON Calendar.[ID] = [Job Spec].[ID] WHERE (((Calendar.[Job Date])= " & Me![JD] & "));"

Set rs = CurrentDb.OpenRecordset("Calendar", dbOpenDynaset)

rs.MoveFirst

If rs.BOF And rs.EOF Then

  rs.AddNew

  rs![Job Date] = [Forms]![Main]![JD]

  rs![NextBusinessDay] = [Forms]![Main]![NBD]

  rs.Update

  Me.Requery

  Else

     Do While Not rs.EOF

    If rs![Job Date] = [Forms]![Main]![JD] Then 'NOTE FIELD NAMES ARE REFERENCED BY ![]

    Set rs = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

    End If

rs.MoveNext 'THIS MOVES TO THE NEXT RECORD IN THE RECORDSET

Loop

End If

If rs.EOF Then

rs.AddNew

  rs![Job Date] = [Forms]![Main]![JD]

  rs![NextBusinessDay] = [Forms]![Main]![NBD]

  rs.Update

  Me.Requery

End If
 

'CLEAN UP MEMORY AT END

If Not rs Is Nothing Then

   rs.Close

   Set rs = Nothing

End If

End Sub

Open in new window

0
 

Author Comment

by:dodyryda1
ID: 24139843
think this is quite difficult so increasing points
0
 
LVL 14

Expert Comment

by:RDWaibel
ID: 24140475
It is because there needs to basically be a rewrite of the main form.
0
 

Author Comment

by:dodyryda1
ID: 24155335
Hi Rd ..

Have rewritten the Main Form recordset is the calendar table and subform is collect from an sql statement how can I get my form to display the record on my form if the If rs![Job Date] = [Forms]![Main]![JD] Then
display the record on the form..
0
 

Accepted Solution

by:
dodyryda1 earned 0 total points
ID: 24159445
solved!! with code below finally nailed the beast!
Private Sub Calendar5_Click()

Me![JD] = Me!Calendar5.Value

Me![NBD] = GetBusinessDay([JD], 1, "23456", 1, "Holidays", "Holiday Dates")

Me.Refresh
 

Dim rs As DAO.Recordset

Set rs = Me.Recordset.Clone

If rs.BOF And rs.EOF Then

  rs.AddNew

  rs![Job Date] = [Forms]![Main]![JD]

  rs![NextBusinessDay] = [Forms]![Main]![NBD]

  rs.Update

  Me.Requery

  Me.Refresh

Else

  rs.FindFirst "[Job Date] = " & "'" & Me![JD] & "'"

   If rs.NoMatch Then

    rs.AddNew

    rs![Job Date] = Me![JD]

    rs![NextBusinessDay] = Me![NBD]

    rs.Update

    Me.Requery

    rs.MoveLast

    Me.Bookmark = rs.Bookmark

    Me.Refresh

   Else

    Me.Bookmark = rs.Bookmark

    Me.Refresh

   End If

End If

    

'CLEAN UP MEMORY AT END

If Not rs Is Nothing Then

   rs.Close

   Set rs = Nothing

End If
 

ExitProc:

   Exit Sub

ProcError:

   MsgBox "Error: " & Err.Number & ". " & Err.Description

   Resume ExitProc

End Sub

Open in new window

0
 
LVL 14

Expert Comment

by:RDWaibel
ID: 24159897
GREAT JOB!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Hide vba in gp 7 43
Dateadd 3 22
Access 2016 Merge Tables 8 28
How to calcualate lateness in Access 2010 11 27
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now