Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    In a previous post Post 739114, I had trouble explaining what I wanted to achieve, but I've totally stripped down the database, and hope I can explain better. I want to enter details into the Release Note Xray form. After entering the date, order No, I type in the Batch No in the sub form. This then retrieve details from either the tblXrayImport or tblXrayImportAli, (the data could be in either). The tables Stock1, tblXrayImport, and tblXrayImporAli, would normally be linked tables, but I've copied them into the database, so I can attach it.
    The problem I have, is when I get to records like Batch No 14989. As you will see from the Release Note Xray form, there is only 1 line entry for this Batch No. In tblXrayImport, there are 4 line entries. DLookup, which I am using, will only pick up the first entry, and if I enter the same batch no underneath, I will get a warning message, as we don't allow the Xray numbers for the same part to be the same (for reasons I can't explain).
    I want to be able to type the Batch No in just once, and if there are 4 entries in the tblXrayImport, they are all put into the Release Note Xray Form, or any other method of getting all the results, without the warning message. Hope this explains it better.[attachment=82622:Autofill_Test.zip]
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='robm' date='04-Mar-2009 11:42' post='763298']
    Rob,

    you need to forget about dlookup() and start using recordsets. Something along these lines, copied for each data item, should do the trick:

    Dim rsGetIWO As DAO.Recordset
    Dim strSearchBatchNo As String
    Dim strSQL As String

    strSearchBatchNo = Nz(Me.BATCH_No) ' put the value of the screen's control into a variable
    If strSearchBatchNo = "" Then ' an entry has been deleted, not added
    Exit Sub
    End If

    strSQL = "SELECT [Xray No] from [tblXrayImport] WHERE [BatchNo] = '" & strSearchBatchNo & "';"
    Set rsGetIWO = CurrentDb.OpenRecordset(strSQL)

    If Not rsGetIWO.EOF Then
    rsGetIWO.MoveFirst
    Do While Not rsGetIWO.EOF
    varIWO = rsGetIWO(0) ' only one field is returned from the recordset, so zero is appropriate
    'do whatever you want with the value here, before moving to the next item
    rsGetIWO.MoveNext
    Loop
    Else
    ' add any more error handling here in case value not found
    End If


    (indents seem to get lost in the posting, add them as required)

    Regards,

    Jules

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='JulesG' post='763335' date='04-Mar-2009 15:20'](indents seem to get lost in the posting, add them as required)[/quote]
    To preserve indentation, place code between [code] and [/code] tags.

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post

    Post

    Noted, thanks.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Just noted, in the code, there is only mention of tblXrayImport, and not tblXrayImportAli. Do I put this code in the After Update event of each control?

  6. #6
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Rob,

    the code currently is in the After Update event of the Batch No field. I see no reason to change this. To replace the 'alternative' part of your original Dlookup(), add a second search as shown. This assumes that you do not need to search in the second table if a record is found in the first table.

    Code:
    If Not rsGetIWO.EOF Then
    		rsGetIWO.MoveFirst
    		Do While Not rsGetIWO.EOF
    			varIWO = rsGetIWO(0)
    				'do whatever you want with the value
    			rsGetIWO.MoveNext
    		Loop
    Else   ' EOF, i.e. no records found in first table
    	   'perform a similar search on tblxRayImportAli
    
    End If
    Regards,

    Jules

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Forgive my ignorance, but I've never dealt with recordsets before. Do I need to copy all of the code to fill in the other controls, or just part of it?

  8. #8
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Rob,

    A recordset is just (?) a query. Design your query in the query grid, change to SQL view, copy the SQL, and paste it where you need it.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've got this code in the AfterUpdate, and I'm still only getting 1 instance of the Part No (I haven't worked out how to get the other data in yet)

    Code:
    Private Sub BATCH_No_AfterUpdate()
    [tab][/tab]Dim rsGetIWO As DAO.Recordset
    [tab][/tab]Dim strSearchBatchNo As String
    [tab][/tab]Dim strSQL As String
    
    [tab][/tab]strSearchBatchNo = Nz(Me.BATCH_No) ' put the value of the screen's control into a variable
    [tab][/tab]If strSearchBatchNo = "" Then ' an entry has been deleted, not added
    [tab][/tab]Exit Sub
    [tab][/tab]End If
    
    [tab][/tab]strSQL = "SELECT tblXrayImport.PartNumber, tblXrayImport.Qty, tblXrayImport.BatchNo, tblXrayImport.[Xray No]FROM tblXrayImport WHERE [BatchNo] = '" & strSearchBatchNo & "';"
    [tab][/tab]Set rsGetIWO = CurrentDb.OpenRecordset(strSQL)
    
    If Not rsGetIWO.EOF Then
    [tab][/tab]rsGetIWO.MoveFirst
    [tab][/tab]Do While Not rsGetIWO.EOF
    [tab][/tab]varIWO = rsGetIWO(0) ' only one field is returned from the recordset, so zero is appropriate
    [tab][/tab]Me.PART_NO = varIWO 'do whatever you want with the value here, before moving to the next item
    [tab][/tab]rsGetIWO.MoveNext
    [tab][/tab]Loop
    'Else
    ' add any more error handling here in case value not found
    End If
    
    
    
    End Sub
    I should be seeing the part number and Batch No on 4 line entries (as in tblXrayImport) shouldn't I?

  10. #10
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='robm' post='763844' date='06-Mar-2009 11:44']I've got this code in the AfterUpdate, and I'm still only getting 1 instance of the Part No (I haven't worked out how to get the other data in yet)

    Code:
    Private Sub BATCH_No_AfterUpdate()
    [Deleted]
    strSQL = "SELECT tblXrayImport.PartNumber, tblXrayImport.Qty, tblXrayImport.BatchNo, tblXrayImport.[Xray No]FROM tblXrayImport WHERE [BatchNo] = '" & strSearchBatchNo & "';"
    Set rsGetIWO = CurrentDb.OpenRecordset(strSQL)
    
    If Not rsGetIWO.EOF Then
    	 rsGetIWO.MoveFirst
    	 Do While Not rsGetIWO.EOF
    		 varIWO = rsGetIWO(0) ' only one field is returned from the recordset, so zero is appropriate
    		 Me.PART_NO = varIWO 'do whatever you want with the value here, before moving to the next item
    		 rsGetIWO.MoveNext
    	 Loop
    'Else
       ' add any more error handling here in case value not found
    End If
    
    End Sub
    I should be seeing the part number and Batch No on 4 line entries (as in tblXrayImport) shouldn't I?[/quote]

    Rob,

    OK, what's happening is the code is looping through the entries nicely, but.... it is just overwriting the value on screen every time. There's nothing to write/move to a new record. There's at least two ways you could approach this problem. If I was starting from scratch, I'd ask for input into unbound controls rather than into a subform. I'd then use the data to append rows directly to the table, and requery the subform to display the new data.

    If we stick with your original logic, you need to update the existing record the first time round the loop, and then append a new record every subsequent time round the loop. So, you need either a counter within the loop, using this to decide which action is appropriate:
    Start intCount at 0 outside the loop.

    Code:
    intCount = intCount + 1
    varIWO = rsGetIWO(0) 
    If intCount =1 then
       'write to the existing record
    Else
       'write to the new record
    End If
    Or, you can use a boolean (True/false) variable, that starts off false and becomes true AFTER the first record has been written.

    You'll probably want to add the Date and Order Number from the original record into any new record, so take them into variables early on in your code. Once you move to a new record, they're not available!

    Not a one-line answer, but there are several steps involved. Go for it!

    Jules

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    What do you really want to achieve here?
    You say you want to copy all the tblXrayImport (or tblXrayImportAll) table entries (4 for BatchNo = 14989 say) from tblXrayImport into the table [RELEASE NOTE X-RAY DETAILS] which is the record source of the sub form. Is that right?
    If that is the case I dont think your table structure is right.

    I think your table structure should be:

    table [RELEASE NOTE X-RAY DETAILS] record source of MF (Main Form)
    SerialNo
    OrderNo
    Date

    table [RELEASE NOTE X-RAY DETAILS] record source of SF1 (subform 1)
    rnxdID (PK to link to table below)
    SerialNo
    PartNo
    Description
    BatchNo
    DateField
    Qty_Released
    other fields like Y, M, YearCode, Monthcode
    etc

    table [RELEASE NOTE X-RAY DETAILS BATCHES] record source of SF2 (subform 2)
    rnxdbID (PK for this table)
    rnxdID (used to link back to [RELEASE NOTE X-RAY DETAILS]
    Qty
    StartValue
    ReferenceNo
    XRayNo

    This means that all the batch info is held in this 3rd table.

    If this is right then your form will have another subform (SF2) for the 3rd table here. When you enter the BatchNo into the subform (SF2) for [RELEASE NOTE X-RAY DETAILS BATCHES] it will signal the AfterUpdate event of the subform (SF2) to write associated records from either tblXRayImport or blXRayImportAll into the table [RELEASE NOTE X-RAY DETAILS BATCHES] which is the record source of the 2nd sub form (SF2).

    If i have not assumed correctly can you let me know (in simple terms so i can understand) what you really wish to do.

    If, on the other hand, you are happy with the path you and Jules are going please disregard this post.

  12. #12
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Basically, what the database does is, the user enters an IWO No. This then pulls in data from tblXrayImport or tblXrayImportali (whichever contains the matching IWO No). This data is Xray Nos for the IWO. So say we type in 15677, the data would bring up EJ 31-32, which was the first instance of this IWO. Since then other quantities have be added to the Xray table, and last record is 15677 EJ 39-43. So when we create a new release note, we want it to state 15677 EJ 39-43. Instead, we get 15677 EJ 31-32, and a message telling us that this number has already been used (which is right)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •