Data not showing on page in Base

[LSemmens]

I had a weird issue in a Base Macro that I have managed to resolve but thought it might be interesting to others who have encountered similar.

I have developed a macro to copy portions of a record to a new record. It all works as intended in that the data is copied but some of the changes are not reflected on the form. It seemed strange that the SAME data was not being displayed every time I copied it. If I proceeded to move to another record and back again the data shows as it should. To me, it indicated that the page needed a refresh or reload after the data was updated, even though all other fields were displayed as copied. Anyway, to cut a long story short. I played around with the order in which the fields were updated and it all now works as intended. Why changing the update order solves the issue is beyond me, but, it now works as intended.

So, if you have troubles with some fields that you know should contain data, not showing, play around with the load order.

Here is the code with appropriate commentary for your info - I have deleted references to about 40 other fields just to make it easier on the eyes

Sub DuplicateRecord(oEvent)
	Dim oForm     'Reference to the form containing the primary record.
	Dim oSubForm  'Reference to the subform containing the data
	Dim CurrRecord
	   
	Dim lCountryID  'Columns used to hold the Field in the Master table.
	Dim lSeries
	Dim lColour
	Dim lValuation_MUH         'this one works fine
	Dim lPerfID
	Dim lBulletinNo
	Dim lValuation_Used                                    'This was problematic
	Dim lValuation_VFU                                     'This was problematic too
	
	Dim sCountryID$  'Value stored in the current field.
	Dim sSeries$
	Dim sSize$
	Dim sColour$
	Dim sValuation_MUH$
	Dim sPerfID$  
	Dim sBulletinNo$
	Dim sValuation_Used$                                               'Part of the problem
	Dim sValuation_VFU$	                                        'Another part of the problem
		
		REM Get the button's view model from the event Source.
		REM Get the button's data model from the view model.
		REM Get the form from the data model.
    
   		oForm = ThisComponent.DrawPage.Forms.getByIndex(0)    ' Main Form
  	 	oSubForm = oForm.GetByName("Stamp")              ' SubForm Name

		CurrRecord = oSubForm.getRow()      				'returns the current record number

	 	 lCountryID = oForm.findColumn("C_NAME")  ' Find the column that contains the NAME field.
 The next line started working when I relocated this here

	         lValuation_Used = oSubForm.findColumn("SM_VALUATION_U") 

This next  one only started to work here - in testing it was before the previous line and did not work.

                lValuation_VFU = oSubForm.findColumn("SM_VALUATION_VFU")  
	        lSeries = oSubForm.findColumn("SM_SERIES_ID")
	          lSize = oSubForm.findColumn("SM_SIZEID")
	 	    lColour = oSubForm.findColumn("SM_COLOUR_ID")
	 lValuation_MUH = oSubForm.findColumn("SM_VALUATION_MUH")
		    lPerfID = oSubForm.findColumn("SM_PERFID")  
			lBulletinNo = oSubForm.findColumn("SM_BULLETIN_ID")
	
		REM Get the data for the current record.
		REM The ID is an autovalue, so we only need to save the other DATA
	     sCountryID = oForm.getString(lCountryID)
	        sSeries = oSubForm.getString(lSeries)
	  		  sSize = oSubForm.getString(lSize)
	  		sColour = oSubForm.getString(lColour)
     sValuation_MUH = oSubForm.getString(lValuation_MUH)
	  		sPerfID = oSubForm.getString(lPerfID)
			sBulletinNo = oSubForm.getString(lBulletinNo)
	sValuation_Used = oSubForm.getString(lValuation_Used)        'I moved these down here to test
     sValuation_VFU = oSubForm.getString(lValuation_VFU)             ' Ditto
  	 
  		REM Now, move to the "Insert row" to add a new record.
		oSubForm.moveToInsertRow()
		
		REM Update all of the data from the original record that you stored.
		REM If I only updated the control, then the form would not know 
		REM about it, but if I update the column in the row, then the control
		REM is automatically updated.
		oSubForm.updateString(lValuation_VFU, sValuation_VFU)
		oForm.updateString(lCountryID, sCountryID)
		oSubForm.updateString(lValuation_Used, sValuation_Used)
		oSubForm.updateString(lSeries, sSeries)
		oSubForm.updateString(lSize, sSize)
		oSubForm.updateString(lValuation_MUH, sValuation_MUH)
		oSubForm.updateString(lPerfID, sPerfID)
		oSubForm.updateString(lBulletinNo,sBulletinNo)	

	End Sub

As you can see, there’s a lot going on and it now works, but why? in that order? Initially the fields were being copied in the order they were stored in the structure of the master table. I checked and double checked all spelling to make sure nothing was wrong, but the data was being copied correctly, just some of it not displayed on the form.

Anyway, if this helps someone, I’ll be happy.

[Ratslinger]

Hello,

First let me state posting solutions to some problem you resolved is always welcome. Another method is to post as a question and then to post your findings as an answer - you can answer your own question. Regardless a +1.

Now I would also like to offer some possible help for your future endeavors.

First I have not experienced any problem as you state simple because of the sequence of which fields were entered. To clarify, this is normal fields and not key fields of otherwise reliant fields. What problem you may have had can only be resolved with the original code and Base files.

As for moving data into controls, to see the data in the control you must use its’ ‘view’, and for it to apply to the record you need to also commit it: myControl.commit(). If not committed this needs to be manually done with the data on the form. Control selected and then Enter. The method used depends upon the situation involved. A sample of the no commit method can be found in my answer here → base auto populate new record. This sample contains code to get a controls’ view.

Now another method, and one which would save you a lot of the coding you did, is to use SQL. A sample of this can be found in my answer on this post → Base: Duplicating a Record in a database. Now although this sample duplicated a record, the concept can be used to copy information from one record to another. Once the record is copied, the form is reloaded. There are various answers in the forum regarding positioning a record if that is needed.

Hope this presents some different insights.

[Ratslinger]

On a side note, you briefly mention deleting references to about 40 other fields. Seems like a lot a fields for a record. Have you normalized the DB?

[LSemmens]

YES, it is well and truly normalised some of the “repeated” data is from other tables but most is unique to only a few records. I’m an old dBase programmer from way back (starting with dBase II). Have a look at this image of my relationships this image which will give you some idea of the task.