Record Set will not set new value to allocated field

[devarts]

In a form I have a bound object that is related to a field (Batch Number) within this table called tblDrumResults.

What I would like for it to do is by choosing a batch number to automatically populate the next field underneath batch number which is kiln.

The criteria for this is that I need to find the batch number that is equivalent to the current batch number I have in another database, in another table called "tblKilnData", because that database currently holds the kiln value I need. So once I have found where the batch number is the same, I need to see in that particular record what the value is for the Kiln Set (another word for Kiln field). If the value for Kiln Set = 11 I need it to populate the value of "A" into the Kiln field and if the value of Kiln Set = 12 I need it to populate a value of "B" for Kiln.

I gave it a shot using this recordset code that I coded myself. There are not any syntax errors but its not populating the information I asked for. Any thoughts?

 Private Sub Kiln_Click()
   Dim dbs As DAO.Database
    Dim rsGen3 As DAO.Recordset
    Dim rsDrumResults As DAO.Recordset
 
    Set dbsGen3 = CurrentDb
    Set rsKilnData = dbsGen3.OpenRecordset("tblKilnData")
    Set rsDrumResults = dbsGen3.OpenRecordset("tblDrumResults")
 
    With rsKilnData
        Do While !Gen3Run = rsDrumResults![Batch Number]
           If ![Kiln Set] = 11 Then
 
                    rsDrumResults!Kiln = "A"
 
                ElseIf ![Kiln Set] = 12 Then
 
                    rsDrumResults!Kiln = "B"
                Else
 
            End If
        Loop
        .Close
    End With 'rsKilnData
    dbsGen3.Close
End Sub
 

[zmbd]

- You talk about two different databases and yet your code only refers to one database file.
- Your loop at 10 never steps thru any records... and I'm not sure what your trying to do there anyway.

- Maybe take a look at this:> Database Normalization and Table Structures. as I feel that there just isn't something right about your database structure.

[devarts]

1) I'm sorry I didn't explain that I linked the tale called tblKilnData from the other database to the Gen3 database. That is why I did not define in the code, but If I still need to define it, even though its linked, I can do that.

2) I had a feeling that could be the case. I didn't quite know how to go about do that part. I want to find the value of the Batch number that matched the batch number in the Gen 3 database. Once it found it, I would like to pull information from that record. But I wasn't sure how to exactly go about that.

3) And all the databases that I work with are already created except for the Gen 3 database. So any structural issues I will have to just live with.

[zmbd]

devarts

Sometimes I'm a little slow at things... blame the Chemist in me (^_^)

Just to make sure that I properly understand how things are going:

External Database with a Linked table to the current and we need to match a field in that table:
[tblKilnData]![Kiln]

In the current database (GEN3) we have table and form and we want to relate the information between the [tblKilnData]![Kiln] and this current information crossrefenced by:
[tblDrumResults]![Batch_Number]

so that
[tblDrumResults]![Batch_Number] = 11
then
[tblDrumResults]![Kiln_xref] = "A"

so that
[tblDrumResults]![Batch_Number] = 12
then
[tblDrumResults]![Kiln_xref] = "B"

so that you can then pull/update (etc) information from [tblKilnData]

Is that correct?

If so then with a query, a calculated field, and join I think we can get what you need without any VBA.

[devarts]

Not exactly... everything is correct until you get to "so that
[tblDrumResults]![Batch_Number] = 11
then
[tblDrumResults]![Kiln_xref] = "A... etc"

Just to point out the Batch Number in tblKilnData is called Gen3Run. I am not sure why but thats the naming convention that was put for that field. So that may help a little.

But essentially I would like to do this:

Form with txtBatchNumber --> enter a number --> 200
Where in tblKilnData!Gen3Run does the field = 200
When it is found... under the same record... what value is Kiln Set?
Say the code was able to find the record that matched the batch number of 200 and it correlated the Kiln Set value to be 11

Now that we now its a 11, I want it to return a value for tblDrumResults!Kiln of A for record 200.

Does that make sense or did I just make things more complicated?

[twinnyfo]

devarts,

I know this post has been hanging out there for a while, but I wonder why you couldn't just use a DLookup on your Kiln Table:

Private Sub txtBatchNumber_AfterUpdate()
    Me.txtKilnSet = IIf(Nz(DLookup("[KilnSet]", "tblKilnData", _
        "[BatchNumber] = " & Me.txtBatchNumber), 0) = 11, "A", _
        Nz(DLookup("[KilnSet]", "tblKilnData", _
        "[BatchNumber] = " & Me.txtBatchNumber), 0) = 12, "B", "-")
End Sub

I don't know the exact names of your Text Boxes, but this would eliminate the need for a recordset. It also depends on if your Kiln Set is an actual number versus a number in text format.

Let me know if this hepps!

[zmbd]

Record set vs. the Dlookup would do with number of records and the location of the data which sounds to be in a different type of database/

In anycase, I've been out of the office w/o access to the net for the past few weeks. I had hopped that someone would have jumpped on the cart with us as we rolled into that kiln.

I may be a bit slow in answering things for the next few weeks as I need to get the lab back up to speed. I'm gone and the whole place starts to fray at the ededges...along with my spelling. (^_^)

[twinnyfo]

Then, modifying the OP's original Code:

Private Sub Kiln_Click()
    Dim dbs As DAO.Database
    Dim rsKilnData As DAO.Recordset
 
    Set dbs = CurrentDb
    Set rsKilnData = dbs.OpenRecordset("tblKilnData")
 
    If Not rstKilnData.EOF Then
        rstKilnData.MoveFirst
        Do While Not rstKilnData.EOF
            If rstKilnData!Gen3Run = Me.txtBatchNumber Then
                If rstKilnDat("Kiln Set") = 11 Then
                    Me.txtKiln = "A"
                ElseIf rstKilnDat("Kiln Set") = 12 Then
                    Me.txtKiln = "B"
                Else
                    Me.txtKiln = ""
                End If
                Goto Kiln_Exit
            End If
        Loop
    Else
        Me.txtKiln = ""
        Goto Kiln_Exit
    End If
Kiln_Exit
    rstKilnData.Close
    dbs.Close
    Set rstKilnData = Nothing
    Set dbs = Nothing
End Sub

This code creates a Recordset of tblKilnData, and cycles thorugh the records until it finds the appropriate Batch Number that was entered into the Form Text Field. If no record is found matching those results, then the Kiln Text Box on the Form is set to "".

When this code finds the Batchnumber in the Field Gen3Run, it checks to see what the Kiln Set is. If it matches either 11 or 12, then it sets the Kiln value properly; otherwise, "". Since the recordset found the Batch Number, there is no need to cycle through any remaining records, to the code just exits at that point.

I hope this hepps!