Search code examples
databaseformsms-access-2016

Where is Access form data populating data from

I am currently trying to help a friend out with their invoicing Access database. I have rarely ever used Access and I am having problems figuring out the location of where the form (frmEntry) is pulling its data from. I did not create this setup so I am unsure of how it works. I am trying to figure out where the address information is being pulled from for when a customer is selected in a drop down on a form. I checked the query and it is only pulling the CustomerID and CustomerName, no address. The table does have address fields but none of the customers in the table have any listed, yet there address is populated along with their name in the form. fromEntry

I do see where there is another form (frmCustomer) that has customer and there addresses but I am not sure if the other form is pulling from here, and if so, why can I not find the addresses in any of the tables or datasheet views? frmCustomer

Any direction would be very much appreciated. My end goal is to obtain the customer information (address etc) so that I can insert it into a new database that I am working on

Solution

  • Your data contains linebreaks and a combobox only shows one line per record.

    To show the data you can replace the linebreaks in rowsource.

    SELECT Replace([CustomerName],vbCrLf, " ") as CName FROM table 
' vbCrLf is the VBA constant for linebreaks (Cr - Carrige Return, Lf - LineFeed)

    This is poor database normalization (imagine you want to search for a customer name that is equal to a city, e.g. Paris). Each line should be a separate field in table (and Postcode too). If there is a linefeed for every data (e.g. no street -> empty line), you can just split the data into the new fields.

    'Put this code in a module
'Split function
Public function splitCustomerName(ByVal strCustomerName as String, ByVal index as long) as String
  Dim arrCustomerName As Variant ' or declare a fixed array if you know the number of lines

  arrCustomerName = Split(strCustomername,vbCrLf)

  splitCustomerName = arrCustomerName(index)
End Function

    The query

    UPDATE table SET newCustomerName = splitCustomerName([table].[CustomerName],0)
  , newCustomerStreet = splitCustomerName([table].[CustomerName],1)
  , newCustomerCity = splitCustomerName([table].[CustomerName],2);

    Just create the necessary columns for name, street and city, then run the query. If you delete the CostumerName column and rename the table (e.g. newTable) you can create a query with the oldname of the table, that behaves like your old table.

    SELECT *
  , newCustomerName & vbCrLf & newCustomerStreet & vbCrLf & newCustomerCity as CustomerName 
FROM newTable