Suggestion's Please

[nepotist]

Hi,
I have designed two forms one is a new project form and other is a project edit form. the way I designed the project edit form is by copying the new project form and pasting it and then renaming to the project edit.Both these forms have a subform acutally a same subform.

New Project Form:
this form is related to the subform as by linking a projectID FIELDS IN both the form. user get to in a number the project id is calculated and displayed on the main form and upon selecting other paraments in the subform the project id text is displayed as the value in the main form. My point here is that project id in the subform is nothing but the same as in the main form.

Project Edit form:
It has the same feature of the newproject form.
Now my problem is that when i try to edit the project Id in the project edit form the subform dosent get updated.

What I am trying to do is this:
I already have a project type parameter in adn which changes with time... I have linked this in both the project edit adn also the subform (this being the second parameter for linking the parent and the child form).
I want the user to give a option where they can change the project type in the project edit form to some thing else and which type value in the subform also needs to be updates. which isnt happening...

I know it is a pretty length essay to read.. But I wanted to be clear

Thanks for your input

[CreganTur]

When you edit the ProjectID, is the change being written to the underlying table correctly? If so, then it may be something as simple as requerying/updating the subform to reflect the change to the table.

[nepotist]

Hi Randy...
Well I am more concerned about the Type this is what the user would be allowed to change and I will be locking the projectID as this a primarykey.

I had an idea of doing it through VBA
some thing like this
Dim Ptype As String
Dim rstproTrips As Recordset
Dim projectID As Long
Dim db As Database

Set db = CurrentDb()
Set rstproTrips = CurrentDb().OpenRecordset("tbltripdairy", dbOpenTable)
Ptype = Form_ProjectEdit.cmbType.Value
projectID = Form_ProjectEdit.txtConcurrencyID.Value

rstproTrips.MoveFirst

rstproTrips.Index = "ConcurrencyID"
rstproTrips.Seek "=", projectID


and then updaitng the type field in here.
but the table that I am trying to index doesnot have a primary key, and I believe the above code wouldn't work as there are repetitions of the project ID.

Can i query the table and then update the dyna set.. if this is possible could you guide me through it

[CreganTur]

Have you thought about making the project Type into a lookup field? That will restrict the user's options and may make it easier for them to change the type on the Edit Form.

[nepotist]

Yahh randy I have a drop down list for the type field. the thing is If i am adding a project it will type A and after some years (eg. after the project completion I need to change it to type B).
Option Compare Database

Option Explicit

Public Sub Typeupdate()

Dim Ptype As String
Dim rstproTrips As Recordset
Dim projectID As Long
Dim db As Database
Dim strsql As QueryDef
Dim qry As String


Set db = CurrentDb()
Set rstproTrips = CurrentDb().OpenRecordset("tblTripDiary", dbOpenTable)
Ptype = Form_ProjectEdit.cmbType.Value
projectID = Form_ProjectEdit.txtConcurrencyID.Value


rstproTrips.Index = "ConcurrencyID"
rstproTrips.Seek "=", projectID
rstproTrips.MoveFirst

Do While rstproTrips!ConcurrencyID.Value = projectID 'here rstprotrips1concurrencyid value = null (that is what I am able to see when I step in to the code other than that there is no error poping up)
rstproTrips!Type.Value = Ptype
rstproTrips.MoveNext
Loop


End Sub



here is the code that I was thinking of aadding it to the afteupdate to the combobox, I dont see any error but at the do while statement though I have a value fo projectid rstprotrips!concurrencyid is being considered to be null and it jumps to the end sub. The other thing is that it dosent do the changes though lol

[nepotist]

Public Sub Typeupdate()

Dim ptype As String
Dim rstproTrips As Recordset
Dim projectID As Long
Dim db As Database
Dim strsql As QueryDef
Dim qry As String


Set db = CurrentDb()
Set rstproTrips = CurrentDb().OpenRecordset("tblTripDiary", dbOpenTable)
ptype = Form_ProjectEdit.cmbType.Value
projectID = Form_ProjectEdit.txtConcurrencyID.Value


rstproTrips.Index = "ConcurrencyID"
rstproTrips.Seek "=", projectID
rstproTrips.MoveFirst
'
Do While rstproTrips.EOF = False
rstproTrips.Edit
rstproTrips!Type = ptype
rstproTrips.Update

rstproTrips.MoveNext
Loop

End Sub


The above code kind a works , the problem is that it updates all the records not just the record with that particular project number...
I tried to remove the rstprotrips.movenext command, in that case it just update the first record of the project number,not the rest .and also the code hangs .

any suggestions ???
:think: :think: :anyone: : pray2:

[nepotist]

I have uploaded a sample , open the form and click the button you will see that all the projects (id) , type have changed to A in the table , But I need only the project (11) to be chnaged to A and 12 to be B.... wheni remove the .movenext command in the code it updates the first adn not the rest and the code hangs

Any help would be appreciated
Thank you

[CreganTur]

This is untested but the error could be that you are moving away from the recordset that you created using the seek method. Also your loop is setup to change the ptype for every single record in your recordset.



Public Sub Typeupdate()

Dim ptype As String
Dim rstproTrips As Recordset
Dim projectID As Long
Dim db As Database
Dim strsql As QueryDef
Dim qry As String


Set db = CurrentDb()
Set rstproTrips = CurrentDb().OpenRecordset("tblTripDiary", dbOpenTable)
ptype = Form_ProjectEdit.cmbType.Value
projectID = Form_ProjectEdit.txtConcurrencyID.Value


rstproTrips.Index = "ConcurrencyID"
rstproTrips.Seek "=", projectID

rstproTrips.Edit
rstproTrips!Type = ptype
rstproTrips.Update


End Sub

[nepotist]

I did try that randy I experiment every possible way with my code and no result.... I retried the code that you sent ... it works but it dosent update all the records with that project number. it does only for the first record.:think:
:banghead: :banghead:

[CreganTur]

Okay- found the issue.

The Seek method is arbitrary- it only finds the first instance of a record with the specified constraints and makes that record the current record. IF you move away from that record, then you are moving through the recordset normally.

You need to use a SQL statement as a part of your recordset's OpenRecordset method to open a recordset that contains only records that match your ProjectID.

[nepotist]

Option Explicit

Public Sub Typeupdate()

Dim ptype As String
Dim rstproTrips As Recordset
Dim projectID As Long
Dim db As Database
Dim strsql As QueryDef
Dim qry As String

ptype = Form_ProjectEdit.cmbType.Value
projectID = Form_ProjectEdit.txtConcurrencyID.Value

qry = "SELECT TBLTRIPDIaRY.CONCURRENCYID,tbltripdiary.linknumber,tbltripdiary.type from tbltripdiary where tbltripdiary.concurrencyid = projectid"

Set db = CurrentDb()
DoCmd.DeleteObject acQuery, "ptypeupdate"
Set strsql = db.CreateQueryDef("pTypeUpdate", qry)

'ptype = Form_ProjectEdit.cmbType.Value
'projectID = Form_ProjectEdit.txtConcurrencyID.Value

Set rstproTrips = CurrentDb().OpenRecordset("ptypeupdate", dbOpenDynaset) ' here it gives me a too few parameters expected 1.
dont know what does mean

rstproTrips.Index = "ConcurrencyID"
rstproTrips.Seek "=", projectID
rstproTrips.MoveFirst
'
With rstproTrips!ConcurrencyID = projectID
Do While rstproTrips.EOF = False
rstproTrips.Edit
rstproTrips!Type = ptype
rstproTrips.Update

rstproTrips.MoveNext
Loop
End With

End Sub

it gives me a error too few parameters, expected 1 ?? I have never pulled a recordset using sql in vba code.. so excuse me if my syntax is completely wrong

[CreganTur]

You don't need to touch the query def at all for this. Just use:

Set db = CurrentDb
rstproTrips = db.OpenRecordset("SELECT
tblTRIPDIaRY.CONCURRENCYID, tbltripdiary.linknumber,
tbltripdiary.type from tbltripdiary where tbltripdiary.concurrencyid = projectid;")

it's that simple- when you provide the SQL statement, DAO will use the SQL statement to query your database and pull the desired records. You may have to insert all of the parethesis to enfore correct SQL syntax to get it to work- try it and see.

[nepotist]

Hey Randy it still gives me Too few parameters error. ate rstprotrips=db........

[CreganTur]

Hey Randy it still gives me Too few parameters error. ate rstprotrips=db........

Please post your new code.

[nepotist]

Option Compare Database

Option Explicit

Public Sub Typeupdate()

Dim ptype As String
Dim rstproTrips As Recordset
Dim projectID As Long
Dim db As Database
Dim strsql As QueryDef
Dim qry As String

ptype = Form_ProjectEdit.cmbType.Value
projectID = Form_ProjectEdit.txtConcurrencyID.Value

'qry = "SELECT TBLTRIPDIaRY.CONCURRENCYID,tbltripdiary.linknumber,tbltripdiary.type from tbltripdiary where tbltripdiary.concurrencyid = projectid"

Set db = CurrentDb()
'DoCmd.DeleteObject acQuery, "ptypeupdate"
'Set strsql = db.CreateQueryDef("pTypeUpdate", qry)

'ptype = Form_ProjectEdit.cmbType.Value
'projectID = Form_ProjectEdit.txtConcurrencyID.Value

Set rstproTrips = db.OpenRecordset("SELECT tbltripdiaRY.CONCURRENCYID,tbltripdiary.linknumber,tbltripdiary.type from tbltripdiary where tbltripdiar.concurrencyid = projectid;")

rstproTrips.Index = "ConcurrencyID"
rstproTrips.Seek "=", projectID
rstproTrips.MoveFirst
'
'With rstproTrips!ConcurrencyID = projectID
'Do While rstproTrips.EOF = False
rstproTrips.Edit
rstproTrips!Type = ptype
rstproTrips.Update

'rstproTrips.MoveNext
'Loop
'End With

End Sub

[nepotist]

I tried different ways bu Unable to figure out why I am getting the runtime error. I did copy paste the sql statement in the query design and it worked fine.

I tried to assign a value in the where condition instead of the projectid and it gave me a datatype mismatch.
The filed concurrencyd is a long integer in the table and i assigned it as long in the VBA.. I guess it is one and the same.. cant think of anything else why this is happening

[CreganTur]

I think the issue is due to a couple of different things.

First, you're getting the parameters error because you're not providing a parameter at all in your SQL string. You have the variable in place, but you didn't break it out of the SQL string to show VBA that it is a variable. I wrapped it as a string because I think I remember from an earlier post that you said it was a string data type (and I'm too lazy to go back and look:rofl: )

Next, you don't need to do anything with the index and you don't need to seek for your project id because the entire recordset contains only records that match your desired project id. This means that you can just loop through all of the available records and make your changes until you reach eof.

Dim ptype As String
Dim rstproTrips As Recordset
Dim projectID As Long
Dim db As Database

ptype = Form_ProjectEdit.cmbType.Value
projectID = Form_ProjectEdit.txtConcurrencyID.Value

Set db = CurrentDb()
Set rstproTrips = db.OpenRecordset("SELECT tbltripdiaRY.CONCURRENCYID, " _
& "tbltripdiary.linknumber,tbltripdiary.type " _
& "from tbltripdiary where tbltripdiar.concurrencyid = '" & projectid & "';")

rstproTrips.movefirst
Do Until rstproTrips.EOF
rstproTrips.Edit
rstproTrips!Type = ptype
rstproTrips.Update
rstproTrips.movenext
Loop


HTH:thumb

[nepotist]

I figured that loop thing and index thing when I was playing around with it. There was one error with your SQL statement , it gave me a data type mismatch in the criteria expression, I went a head and removed the single quotes and it works perfect.
Could please explain or refer me to a article or a link that to explain me about the how to use the variable in thec irteria expression and when to use single quote and double Quotes..

Thank you for taking time randy... I will buy you a beer some day lol :)

[CreganTur]

Could please explain or refer me to a article or a link that to explain me about the how to use the variable in thec irteria expression and when to use single quote and double Quotes..


I've been planning to write an article for the site on this one day since it's a very common issue... guess I need to finally do that!

SQL and VBA do work together very well, but when you are referencing a VBA variable in your SQL string you have to concatenate it so that VBA can see taht you want the value of the variable to be referenced in the SQL string. Otherwise, if you leave the variable inside the SQL string without breaking it out, then SQL will take your variable as a literal string value. That's why I broke it out using the & symbols.

Whenever you use a variable within a SQL string you have to tell SQL what data type the variable is. To do this you wrap it with symbols. In the example I gave above, I thought that projectid was a string value- you wrap all strings with single quotes (as shown above) so that SQL knows it is a string. If you are using dates, then you would use pound signs (#) instead of single quotes. If you are using a number data type, then you do not use any symbols.


Thank you for taking time randy... I will buy you a beer some day lol :)
Ahhh beer... the universal currency:beerchug:

[nepotist]

Once you do write it and is accepted give me the link....