Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

RS findfirst method without clone

Status
Not open for further replies.
ItIsHardToProgram

ItIsHardToProgram

Technical User
Mar 28, 2006
946
CA
I was wondering if there was a way to bookmark a certain record without passing through a clone with this code:

Code: 
dim rs as object

Set rs = Me.Recordset.Clone
    rs.FindFirst "[Date1] = " & "#" & Str(Nz(startdate)) & "#"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

And instead using something like this:
Code: 
Set DB = CurrentDb()
Set rs = DB.OpenRecordset("Ftemps", dbOpenDynaset)
 
 strWhere = "[Date1] = " & "#" & Str(Nz(startdate)) & "#"
 rs.FindFirst strWhere
 If rs.NoMatch Then
rs.AddNew
rs![Date1] = Str(Nz(startdate))
rs![Date2] = Str(Nz(enddate))
rs![Nom] = Y
rs![PreN] = z
rs.Update

 End If
 If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.Close
DB.Close
Set rs = Nothing
Set DB = Nothing

Regards, Julien

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Sort by date Sort by votes
I forgot to mention what problem I was getting. With the 2nd code, the one I am using, when I select a week, it goes to the specific week but once I have it selected I can't go to any other week, the combobox is frozen.... so I would need to fix that, but I can't seem to put my finger on the problem. Also, when I try to add a new record and put information in a bounded txtbox it says that I haven't call a cancelupdate or a addnew event.... what does that mean...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
I have tried to remove all bounds on my forms except the dates since I want them shown at all time and control this via recordset, the code now looks like this:

Code: 
Private Sub SelSem_AfterUpdate()
      ' Rechercher l'enregistrement correspondant au contrôle.
    Dim DB As DAO.Database
    Dim rs As DAO.Recordset
    Dim strWhere As String
  startdate = Me.SelSem.Column(1)
  enddate = Me.SelSem.Column(2)


Set DB = CurrentDb()
Set rs = DB.OpenRecordset("Ftemps", dbOpenDynaset)
 
 strWhere = "[Date1] = " & "#" & Str(Nz(startdate)) & "#"
 rs.FindFirst strWhere
 If rs.NoMatch Then
rs.AddNew
rs![Date1] = Str(Nz(startdate))
rs![Date2] = Str(Nz(enddate))
rs![Nom] = Y
rs![PreN] = z
rs![no_emp] = EmpNumber()
rs.Update

 End If
 If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.Close
DB.Close
Set rs = Nothing
Set DB = Nothing


'date1F = startdate
'Date2F = enddate

End Sub

Now I am not even able to modify the cbobox Please help me!!! I am going back to the university next week and I need the database functional before I leave.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
Ok so even though I am the only one posting in this thread, I will update what errors or problems I have with this code. I can now effectively select an already entered week with this findfirst method, but apparantly it won't fire my 2nd event, rs.nomatch, so it got me wondering, when using findfirst with a date, will it try to find the first date from the date mentioned? if so I tried with 2006-12-26 which has no record obviously and it still won't add... I requeried and refreshed my form to see if maybe that was the small problem but no... apparantly my addrecord event won't fire.... addrecords are enabled, suppose to be enabled.

I am still in the woods.... :(

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
I looked down on this and now I can't add any record in my timesheet, I believe this has something to do with the bookmarks?... ???? any enlightment???

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
I have tried this:
Code: 
Set DB = CurrentDb()
Set rs = DB.OpenRecordset("Ftemps", dbOpenDynaset)
 
strWhere = "#" & Str(Nz(startdate)) & "#"
While Not rs.EOF
If Not rs![Date1] = strWhere Then
rs.MoveNext
MsgBox "step 1"
TestWhile = True
Else
TestWhile = False
End If

Wend
If TestWhile = True Then
MsgBox "step 2"
rs.AddNew
rs![Date1] = Str(Nz(startdate))
rs![Date2] = Str(Nz(enddate))
rs![Nom] = Y
rs![PreN] = z
rs![no_emp] = EmpNumber()
rs.Update
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.Close
DB.Close
Set rs = Nothing
Set DB = Nothing
'date1F = startdate
'Date2F = enddate
Me.Refresh

still no result... apparantly it won't let me add a record, could it be because I have a linked subform????

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
I have tried directly affecting the table, still no joy... I really am starting to get clueless.....



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
Wow, that is a lot of posts.

OK, tell me this: "without going into your solution, tell me what it is that you are trying to do".

What are the requirements?
What are you trying to do?
Give as much (general) detail as possible.



C-D2
 
Upvote 0 Downvote
Sorry I just felt like updating the posts, It is prety simple actually... I have a timesheet named Feuille_Temps_F1 and a subform where you enter the hours per project per day.

The subform is linked to the Main timesheet through a many to 1 relationship.

The timesheet number on the main timesheet is the primary key, and my foreign key is the timesheet number on my subform.

On my main timesheet form I have a filter using the environment property To find a EmpNumber() which is the employee number. You can find the employee number on both the main timesheet form and the subform.

There is a combobox on my timesheet that has a function that calculates every single week for a year and displays them as 19-02-2006 au 26-02-2006 for example. the column for the first entry is column # 1 and the 2nd date is column #2.

I want this combobox to behave as a search engine, which I am well able to do, but if there is noMatch, I want the cbobox to Create a new record, and add the information about the specific employee in that new record, clearing automaticaly of course the subform. I am not able to do this.

I am able to fire the NoMatch event, but the addNew won't activate... I tried relating it to both the query and the table.....

I have a combo box on my timesheet that runs a findfirst code to select a specific record. The one I posted, I will provide the code I am trying to use:

Code: 
Dim DB As DAO.Database
    Dim rs As DAO.Recordset
    Dim strWhere As String
  
  startdate = Me.SelSem.Column(1)
  enddate = Me.SelSem.Column(2)


Set DB = CurrentDb()
Set rs = DB.OpenRecordset("Feuilletemps", dbOpenDynaset)
'strWhere = "[date1] = " & "#" & Str(Nz(startdate)) & "#" & " AND " & "[no_emp] = " & EmpNumber()
strWhere = "[Date1] = " & "#" & Str(Nz(startdate)) & "#"
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "bla bla bla"
TestWhile = True
Else: TestWhile = False
End If
If TestWhile = True Then
MsgBox "step 2 WAYYYY"
rs.AddNew
rs![Date1] = Str(Nz(startdate))
rs![Date2] = Str(Nz(enddate))
rs![Nom] = Y
rs![PreN] = z
rs![no_emp] = EmpNumber()
rs.Update
End If
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.Close
DB.Close
Set rs = Nothing
Set DB = Nothing
Me.Refresh
Me.Requery

I added the refresh and requery just to check but still no results... the bookmark works, just not the addnew and the msgbox fires (step 2 WAYYYYY)

Hope this is more clear and thanks for replying, I am just ripping my hair off my skull.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
If you are using a bound form try rs.edit instead of rs.addnew.

Mike
 
Upvote 0 Downvote
OK, if this were me I would attack this a different way (and probably not the 'best' way either).

OK, so you have 1 form and 1 subform and you want the subform to behave differently.

Instead, have you considered having the "If Then Else" on the main form and having 2 subform? 1 subform that shows the results and the other subform that is for adding a new record?

OK, yet another idea, upon rs.EOF can you change the subform to "Data Entry".

Without seeing your form and subform, I have no idea what to tell you.

Can you somehow send me the database / form / screenshots of the form??!?!??!!?

C-D2
 
Upvote 0 Downvote
I actually found a fix this morning here is what i Did... Apparantly when editing through the recordset the form would not update... since I did not know how to update the form i used this:

Code: 
Private Sub SelSem_AfterUpdate()
      ' Rechercher l'enregistrement correspondant au contrôle.
    
    Dim DB As DAO.Database
    Dim rs As DAO.Recordset
    Dim strWhere As String
    
    TestWhile = False
  
  startdate = Me.SelSem.Column(1)
  enddate = Me.SelSem.Column(2)


Set DB = CurrentDb()
Set rs = DB.OpenRecordset("Ftemps", dbOpenDynaset)
'strWhere = "[date1] = " & "#" & Str(Nz(startdate)) & "#" & " AND " & "[no_emp] = " & EmpNumber()
strWhere = "[Date1] = " & "#" & Str(Nz(startdate)) & "#"
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "bla bla bla"
TestWhile = True
End If
If TestWhile = True Then
MsgBox "step 2 WAYYYY"
AddNewRecord
'rs![date1] = Str(Nz(startdate))
'rs![date2] = Str(Nz(enddate))
'rs![Nom] = Y
'rs![PreN] = z
'rs![no_emp] = EmpNumber()
'rs.Update
End If
If Not rs.EOF And TestWhile = False Then Me.Bookmark = rs.Bookmark
rs.Close
DB.Close
Set rs = Nothing
Set DB = Nothing

If TestWhile = True Then
Set DB = CurrentDb()
Set rs = DB.OpenRecordset("Ftemps", dbOpenDynaset)
strWhere = "[Date1] = " & "#" & Str(Nz(startdate)) & "#"
rs.FindFirst strWhere
If Not rs.EOF And TestWhile = False Then Me.Bookmark = rs.Bookmark
End If

my public routine is the following:
Code: 
Public Sub AddNewRecord()
DoCmd.GoToRecord , , acNewRec
no_em.Value = EmpNumber()
x = no_em.Value
z = CStr(x)
criteria = "no_emplo = " + z
Y = DLookup("Nom", "Emp", criteria)
nomF = Y
Y = DLookup("prenom", "emp", criteria)
prenomF = Y
  startdate = Me.SelSem.Column(1)
  enddate = Me.SelSem.Column(2)
date1F = startdate
Date2F = enddate
End Sub

Even though not alot of people helped :( Im glad I could find a fix, and probably would not have been able to without this site, Thanks anyway :)

regards, Julien

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
Thank you for your reply chanceD2, in fact the way I used is probably not the best way, but it works for now and fills me with great joy :) thank you

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

StevenFromSouth
  • Locked
  • Question
Using .Fields() method in Recordsets for calculations. 1
Replies
1
Views
99
Andrzejek
Andrzejek
TheresAlwaysAWay
  • Locked
  • Question
Put default text into text box without it being stored in the table
Replies
4
Views
83
sxschech
sxschech
TrekBiker
  • Locked
  • Question
Allen Browne's duplicate record method giving error 1
Replies
2
Views
86
TrekBiker
TrekBiker
weightinwildcat
  • Locked
  • Question
How can I create a clone of a record set that is NOT in synch with the original?
Replies
6
Views
58
strongm
strongm
weightinwildcat
  • Locked
  • Question
Update or CancelUpdate without AddNew or Edit.
Replies
1
Views
68
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top