.Edit only edits first entry in table?!

[The Bey]

I have an add form which is supposed to take the info input into a form and then add it to an existing table.

Here is the code that I've used:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tbl06ButamerAllLines", dbOpenDynaset, dbSeeChanges)

With rs
.Edit
rs!UniqueID = Me.txtID
rs!EquipmentNo = Me.txtEquipment
rs!Service = Me.txtService
rs!HydrocarbonsPresent = Me.txtHC
rs!AdditionalAccess = Me.txtAccess
rs!InstrumentType = Me.txtInstrument
rs!ImpulseLineMaterial = Me.txtMaterial
rs!Lagged = Me.txtLag
rs!Traced = Me.txtTraced
rs!DateOfInspection = Me.txtDate
rs!DueDate = Me.txtNewDate
rs!Comments = Me.txtComments
rs!MatActionDueDate = Me.txtMatDate
rs!InsActionDueDate = Me.txtInsDate
rs!TransActionDueDate = Me.txtTransDate
rs!TraceActionDueDate = Me.txtTraceDate
rs!AdditionalAccessToBeInstalled = Me.txtAccess

'Tells the program to add the action dependent on the material
If Me.txtMaterial = "Carbon Steel" Then
rs!MaterialAction = Me.txtCMatRef
Else
rs!MaterialAction = Me.txtSMatRef
End If

If Me.txtMaterial = "Carbon Steel" Then
rs!InsulationAction = Me.txtCInsRef
Else
rs!InsulationAction = Me.txtSInsRef
End If

If Me.txtMaterial = "Carbon Steel" Then
rs!TransitionAction = Me.txtCTransRef
Else
rs!TransitionAction = Me.txtSTransRef
End If

If Me.txtMaterial = "Carbon Steel" Then
rs!TracingAction = Me.txtCTraceRef
Else
rs!TracingAction = Me.txtSTraceRef
End If

.Update
.Close

End With

What's happening is only the first record on my form is being edited. How do I get it to only update at a certain point, this case being "Unique ID", essentially overwriting all previous info at a certain common point?

 

[jzwp22]

I have an add form which is supposed to take the info input into a form and then add it to an existing table.

If you are trying ADD a new record not change an existing record then you need to change this part of your code

From this:

With rs
.Edit

To this:

With rs
.AddNew or .Add (not sure if the command is the same in DAO versus ADO)

 

[The Bey]

Sorry, I used add losely; I meant it in the sense that I would like to add data to an already existing record, hence the .Edit

After looking at it I realise that there's nothing in my code saying what in particular I'm looking to edit, which is the reason it's just editing the first record in the table.

Any input on how I can clarify this?

 

[jzwp22]

You have to somehow which record needs to be edited and then move the recordset to that record and then do the edit. Out of curiosity, why don't you just use a bound form to accomplish this?

 

[The Bey]

Does it require complicated coding to appoint the update to the required record? My coding knowledge is limited... improving, but still limited.

My data is coming from a number of textboxes using information that is calculated from various sums and I'm not sure how to get this all to link into a bound form. I will have a play while I wait for a response

 

[jzwp22]

Here are a couple links to find a record and move through records in a DAO recordset.

http://msdn.microsoft.com/en-us/library/bb243793%28v=office.12%29.aspx

http://msdn.microsoft.com/en-us/library/bb243789%28v=office.12%29.aspx

 

[The Bey]

I had a think and an update query would be quite fitting. What're the down/upsides to using an update query instead of using code?

Thanks for the links, I'm seeing what I can do with them

 

[jzwp22]

I would generally create the update query dynamically in the code and then execute it. You still have to identify which record is to be updated. I do not think you will notice a performance difference between the two methods. The update query approach would probably involve less code.

 

[The Bey]

Yeah I figured I could get away with less code if I used the update. I'm going to create 2 queries and run the relative one through a simple If statement in the OnClick event.

I'm not going to run into any snags with this am I?

Also, how can I populate a text field with another text field's info?

What I'm trying to do here is using a Null statement in my code, I want a text box to be filled with another box's data if another box is empty

If IsNull([Forms]![frmAction]![txtInstrument2]) Then
Me.txtInstrument = [Forms]![frmAction]![txtInstrument]
Else Me.txtInstrument = [Forms]![frmAction]![txtInstrument2]
End If

I haven't run this yet but I know that from previous experience that it will not give me an answer, so how can I get the text string to show up?

 

[jzwp22]

I'm going to create 2 queries and run the relative one through a simple If statement in the OnClick event.

Why would you need 2 queries?

I'm not going to run into any snags with this am I?

Hard to answer this one.

What I'm trying to do here is using a Null statement in my code, I want a text box to be filled with another box's data if another box is empty

You might be more successful if you use the NZ() function

If NZ([Forms]![frmAction]![txtInstrument2], "")="" Then
Me.txtInstrument = [Forms]![frmAction]![txtInstrument]
Else
Me.txtInstrument = [Forms]![frmAction]![txtInstrument2]
End If

On what form is/are the command button and txtInstrument?

 

[The Bey]

Earlier in my form I was having problems with a sum. The outcomes from this sum were dependent on a material chosen, so what I did was make 2 seperate text boxes, each having different responses in them depending on the material chosen. Then I set their default visibility to false and made them true depending on whichever material was chosen.

I've tried going back over this but I can't get the right answer to come out, so I've just run with it.

It's making things more difficult for me but I'm slowly getting around it, which leads me to why I need 2 queries.

So, if the material is material A then the query relating to that runs, otherwise it runs the material B query.

The command button is on the form "frmAddAction" and the txtInstrument is on the form "frmAction". This looks confusing because I've also got a txtInstrument on my "frmAddAction" form... I think I'll change that for something else to prevent confusion.

In this line "If NZ([Forms]![frmAction]![txtInstrument2], "")="" Then" would i leave the " "")="" " as it is or change it according to my requirements? (I've never used it so am puzzled)

 

[Beetle]

My data is coming from a number of textboxes using information that is calculated from various sums and I'm not sure how to get this all to link into a bound form.

Are you going through all this in an attempt to store calculated values in a table?

If so, be aware that in most cases that is not good practice. If any of the underlying data that the original calculation was based on gets changed, the calculated value that you stored in a table will not be automatically updated. You would then have invalid data in your table with no automatic way to detect that fact.

Values like this should be calculated in queries or on forms/reports, not stored in a table.

 

[jzwp22]

I agree with Sean (aka Beetle) about calculated values. In most case they should not be stored in a table.

In this line "If NZ([Forms]![frmAction]![txtInstrument2], "")="" Then" would i leave the " "")="" " as it is or change it according to my requirements? (I've never used it so am puzzled)

This statement just checks to see if there is text in the control. If there is text then it evaluates to false and the code after the ELSE statement executes.

If there is no text (i.e. null), the NZ () function returns "" and the statement evaluates to true and the next statement is executed. You can consult the help section for a more detailed explanation.

 

[The Bey]

No no the calculated values aren't stored; I've been warned of this elsewhere so I haven't done it. I have sums on my form which are calculated depending on the selections made, so they are constantly changing and thus storing the calcs wouldn't be beneficial.

Ok thanks for the update guys, much appreciated.

Something I'm having trouble with now is to do with a query.

I have a couple different values checking to run through a query, and the criteria in each of my columns on the query have "Like "*" & [forms]![myForm]![formName] & "*" " .

What I have also got are a number of buttons and when each is clicked, a text box is filled with relevant information and the other text boxes are given "null" (bare with me, I do have a point to this). What essentially I want to happen is to view all values with specific criteria.

When I first wrote the query, it worked nicely, but then there were 2 records to choose from and the query was coming up blank.

Can you give me an explanation for this? If this isn't making sense then I could post either the SQL or a sample of the DB??

 

[jzwp22]

A sample database would probably be best since we would be able to get a better picture of what you are dealing with.

 

[The Bey]

OK so i've pretty much gutted the DB to exclude any sensitive information, so you should be able to see what I've done with the query and form.

Have a little look through the query and hopefully you'll see what I'm trying to get at.

I've removed a lot of the information that I referred to earlier in the post because some of the stuff could be seen as private, which is a shame. If you'd like I could improve on this one and send an updated one to see if you can help me elsewhere?!

 

[The Bey]

Well it turns out it was my fault that they weren't showing up, and now I've worked out why; I have the criteria set to "*" so that it will only show records which have data in this field... turns out that the records I was looking for didn't have data in this field

 

[jzwp22]

OK, so you have worked out the one issue. Are you still in need of assistance on the initial problem for which you started the post? If so, I will have to look at your database from home this evening since I only have Access 2003 here at work.

 

[The Bey]

Yes the initial dilemma has been dealt with, but a little problem I'm having now is with a report I'm getting to run from the query which I posted in the sample.

It's asking me for criteria when I try to run the report, but when i just click "OK" to each of the questions it comes up with the correct report. The problem is that I haven't stated anywhere in the query the criteria that it's asking me for. I'll have another look in work tomorrow but I can't see why this is happening.

Another problem which I'm having is with a previous query linked to a search form, where the query pulls the data from the search form and displays it in a report. What's happening is it'll pull the data if it has a record in it, but won't pull the data if a certain record is missing.
It's strange because it'll pull other fields that are blank, as long as this column in particular has a value. Can I change the, we'll call it "base" value, so that as long as a certain field has data in it then it'll pull all the other records based on the search form?

I could probably make a gutted DB for this if this is unclear

 

[jzwp22]

In your query, you use the LIKE operator with your inspection date, I don't think that will work. If I remove that criteria, I get a result; if the date criteria is included I get no results.

SELECT tbl06AllLines.UniqueID, tbl06AllLines.DateOfInspection, tbl06AllLines.InsulationAction, tbl06AllLines.InsActionDueDate, tbl06AllLines.TracingAction, tbl06AllLines.TraceActionDueDate, tbl06AllLines.MaterialAction, tbl06AllLines.MatActionDueDate, tbl06AllLines.TransitionAction, tbl06AllLines.TransActionDueDate
FROM tbl06AllLines
WHERE (((tbl06AllLines.DateOfInspection) Like "*") AND ((tbl06AllLines.InsulationAction) Like "*" & [Forms]![frmActionCount]![txtInsResponse] & "*") AND ((tbl06AllLines.TracingAction) Like "*" & [Forms]![frmActionCount]![txtTraceResponse] & "*") AND ((tbl06AllLines.MaterialAction) Like "*" & [Forms]![frmActionCount]![txtMatResponse] & "*") AND ((tbl06AllLines.TransitionAction) Like "*" & [Forms]![frmActionCount]![txtTransResponse] & "*"));

It's asking me for criteria when I try to run the report, but when i just click "OK" to each of the questions it comes up with the correct report. The problem is that I haven't stated anywhere in the query the criteria that it's asking me for. I'll have another look in work tomorrow but I can't see why this is happening.

On what query is the report based?

What's happening is it'll pull the data if it has a record in it, but won't pull the data if a certain record is missing.

I'm not sure I follow you on this so an example would be best. As a caution, you are using AND in your criteria, so every condition must be met to return a record. I don't know if you should be using OR or a combination of OR and AND.