Fetching records

sbaud2003

Member
Local time
Today, 23:41
Joined
Apr 5, 2020
Messages
186
is it possible to fetch a record from a table and post in the text box of the form, for adding adding records. if so hoe can be done
 
Not sure I understand your question. Why not just bind the form to the table?
 
I think we need a little bit more detail of your goal. Access is designed for a form to fetch records, but it sounds like you have the desire to perform a non-standard use of record-fetching. DON'T post code, but DO tell us in a little more detail what you intend to do.
 
I think we need a little bit more detail of your goal. Access is designed for a form to fetch records, but it sounds like you have the desire to perform a non-standard use of record-fetching. DON'T post code, but DO tell us in a little more detail what you intend to do.
Thanks for the concerns sir!
I am adding records for booking accommodation in a Guest House Booking Package. I am using Add Button to add records. But sometimes its so happen that single guest is given two guest rooms with different Booking ID (Auto No)
making entries of all details is somehow time consuming, thus I want if Details cam be fetched from the first record so as to avoid mistakes and save times.

i am using simple code for adding
DoCmd.GoToRecord acForm, Me.Name, acNewRec
 
That code just moves to new record row, does not add data. If you want to 'carry forward' entries, one method is to use VBA to set DefaultValue of controls.

In each control AfterUpdate event:

Me.controlname.DefaultValue = Me.controlname
 
Ah, you wish to PARTIALLY copy information - but not everything. You need to keep the unique parts unique but copy the common parts.

One easy way to do this is to make a combo box that shows you recent bookings so you can select one of those records to become your template. If the desired information is then kept in the extra columns of the combo box, you can use some code, perhaps behind a button-click (that says "COPY FROM SELECTED RECORD") to copy from the columns of that selected combo row into the relevant fields of a new record on the booking form.

Another way is to have a command button that you would use when the data from the first booking is still on screen. Have code behind the button click event that will store relevant customer data in the form's class module declaration area. Then have ANOTHER button such that you can recall the stored customer data. Since you will know ahead of time that you intend to copy that information, you can use the buttons or not, as needed.

Both of these methods will work. Neither one is the preferred method for long-term stability.

Another way that is more technically correct (better normalized) but might require some structural rework is that you FIRST define your customer in a customer table. THEN when you book the rooms, you can use a combo box to select your customer and, instead of repeating the customer info, just store a customer ID for each booking. Then when needed, use the customer ID to retrieve the rest of the information.

The preferred method uses the power of a relational database (which Access is) to split out things in a way to reduce duplication. By keeping the customer data separate from the room data, you accomplish something called normalization. If you have no familiarity with that term, it is time that you did some reading. For this forum, which IS a database site, search for "Normalization." (SEARCH button is top-right in the menu bar.) For a more general web search, use "Database Normalization" because other disciplines use "normalization" differently - for example, in chemistry, politics, economics, psychology, mathematics, ... you get the idea. When reading about normalization, you usually only need to get to 3rd normal form for Access to be perfectly happy.

To build reports (where you expect or even need data duplication) you can build a JOIN query where the booking record and customer record can be logically joined together - only during the execution of the query - to have everything you need for the report in a single (virtual) record.
 
I used the method from Allen Browne (well at least I seem to recall I did :unsure: )

However I had a chkbox which I set if I wanted to carry over the data for several controls.
Worked pretty well for over 27K records? :-)

Code:
Private Sub Rank_ID_AfterUpdate()
    If Me.chkCopy Then
        Me![Rank_ID].DefaultValue = """" & Me![Rank_ID].Value & """"
    Else
        Me![Rank_ID].DefaultValue = 0
    End If

End Sub
 
Ah, you wish to PARTIALLY copy information - but not everything. You need to keep the unique parts unique but copy the common parts.

One easy way to do this is to make a combo box that shows you recent bookings so you can select one of those records to become your template. If the desired information is then kept in the extra columns of the combo box, you can use some code, perhaps behind a button-click (that says "COPY FROM SELECTED RECORD") to copy from the columns of that selected combo row into the relevant fields of a new record on the booking form.

Another way is to have a command button that you would use when the data from the first booking is still on screen. Have code behind the button click event that will store relevant customer data in the form's class module declaration area. Then have ANOTHER button such that you can recall the stored customer data. Since you will know ahead of time that you intend to copy that information, you can use the buttons or not, as needed.

Both of these methods will work. Neither one is the preferred method for long-term stability.

Another way that is more technically correct (better normalized) but might require some structural rework is that you FIRST define your customer in a customer table. THEN when you book the rooms, you can use a combo box to select your customer and, instead of repeating the customer info, just store a customer ID for each booking. Then when needed, use the customer ID to retrieve the rest of the information.

The preferred method uses the power of a relational database (which Access is) to split out things in a way to reduce duplication. By keeping the customer data separate from the room data, you accomplish something called normalization. If you have no familiarity with that term, it is time that you did some reading. For this forum, which IS a database site, search for "Normalization." (SEARCH button is top-right in the menu bar.) For a more general web search, use "Database Normalization" because other disciplines use "normalization" differently - for example, in chemistry, politics, economics, psychology, mathematics, ... you get the idea. When reading about normalization, you usually only need to get to 3rd normal form for Access to be perfectly happy.

To build reports (where you expect or even need data duplication) you can build a JOIN query where the booking record and customer record can be logically joined together - only during the execution of the query - to have everything you need for the report in a single (virtual) record.
sir thank you very much.. i have utilized a combo as you have suggested and my work is done. have a great time
 

Users who are viewing this thread

Back
Top Bottom