recordset edit quiery

[cynic-al]

Hi all



New to the forum, haven't used access in a lot of years and I'm struggling! Hoping someone can point me in the right direction.

I have two tables;

- 'Step List' stores product information, code, drawing number, description etc. The code is unique on each item.
- 'Daily Tester' is for entering QC test data that operators undertake during production.


I have created a form for the daily tester table where they select a part code via a combo box which is looked up from the step list table. It shows them part description etc to confirm they have selected the right part. However I want it to put drawing number into the 'daily tester' table not just look it up each time.

On the code combo box after update I have put the following code;



Private Sub Code_AfterUpdate()


'Code below adds drawing number into daily tester table once step code entered
Dim dwgno As String
Dim db As dao.Database
Dim rs As dao.Recordset


dwgno = DLookup("Drawingnumber", "Step List", "Code='" & [Code] & "'") 'gets the drawing number for the selected code on the daily tester form from the step list table


Set db = CurrentDb
Set rs = db.OpenRecordset("Daily Tester", dbOpenDynaset)

With rs
.Edit
.Fields("Drawing") = dwgno
.Update
End With


rs.Close


Me.Requery
DoCmd.GoToRecord , , acLast


End Sub







This works, it retrieves the correct drawing number from the step list table based on the code selected in the combo box. However, it only ever inserts this drawing number into the first line in the daily tester table regardless of which line you are working in. I need to get it to move down to the line I'm working on but can't seem to find a way around it.

Any advice or pointers of where to look would be appreciated!

[Welshgasman]

One way.
I would bring in the drawing number with the code for the combo, then just refer to that column.
In the current event of the subform, check for NewRecord. If it is one set your field from that column.

This assumes everything is bound, which is how I have always used Access forms.

Another way is set a TempVar as the default for that control in the form, and set that from the combo. Again form needs to be bound.

[moke123]

Set rs = db.OpenRecordset("Daily Tester", dbOpenDynaset)

Use a variable for your rs with a where clause. Access needs to know what record to update

something like

dim strSql as string
strSql = "Select * from DailyTester where SomeField = " & me.SomeField
debug.print strsql

[xps35]

Just don't do it. The drawing number should be stored in Step List and nowhere else. If you need to show the number get it from there.

[June7]

I agree, there is no reason for drawing number to be saved into another table, just save StepList primary key (possibly from an autonumber field). Retrieve drawing number from StepList table when it is needed. One way is to include it as a column in combobox then expression in textbox can refer to that column index just to display the associated drawing number. Column index is 0-based so if drawing number is in 3rd column its index is 2:

=comboboxname.Column(2)

Build query that joins tables in order to display all related info in a report.

Advise not to use space in naming convention.

[cynic-al]

Thanks for the input all, I've taken the suggestions onboard.

The reason for storing the drawing number rather than retrieving it every time is that if a product is revised it maintains the existing part code but gets a new drawing number. If I retrieve the drawing number I will not be able to track which version has been tested. If I store the drawing number I will be able to do this.

I tried a few different ways to get it working, all hit problems with either the code or access. Finally got it working and it turned out to be very simple. Not sure how I made it so difficult.

Dim dwgno As Integer
dwgno = DLookup("Drawingnumber", "StepList", "Code='" & [Code] & "'") 'gets the drawing number for the selected step code from the step list table
Drawing.SetFocus
Drawing.Value = [dwgno]

Thanks for all the advice... onto the next problem!