Adding Data to a Table

[ohappyday]

Hi -

I have searched the Threads for a possible answer, but since I am not sure what the process is, I don't know what to look for.

Here's where I need help...

I have a db which documents a technicans visit to various locations for support. There are is a main form and subforms (Hours, Support Tickets etc.) to capture data based on the Technician's work for a particular week.

I have a subform to capture the many visits (Travel Form linked to the Travel Table), but what I need to do is add a 'Add Another Location' button on the form to allow the technician to add as many locations as they need.

I need the new entries appended to the current table entry (Travel Table) based on the Technicians Name.

What is this process called? How or can this be done?

Thanks!

 

[David R]

Hi -

I have searched the Threads for a possible answer, but since I am not sure what the process is, I don't know what to look for.

Here's where I need help...

I have a db which documents a technicans visit to various locations for support. There are is a main form and subforms (Hours, Support Tickets etc.) to capture data based on the Technician's work for a particular week.

I have a subform to capture the many visits (Travel Form linked to the Travel Table), but what I need to do is add a 'Add Another Location' button on the form to allow the technician to add as many locations as they need.

I need the new entries appended to the current table entry (Travel Table) based on the Technicians Name.

What is this process called? How or can this be done?

Thanks!

Are you trying to add a 'Location' to a drop-down list that they can then use to populate one field in their Travel Logs, or is 'Add Another Location' your process/button for adding an entry to the Travel Log itself? Those are two very different things.

For the first, check a thread like http://www.access-programmers.co.uk/forums/showthread.php?t=175797 or search for "notinlist combobox" (without the quotes, obviously).

For the second, isn't that just DoCmd.GoToRecord , , acNewRec? Or am I missing something in your request?

 

[ohappyday]

Thanks for the quick repsonse!

It's almost like your #2, but adding a new record just adds a new record to the table. I want all of the locations that the person entered for that week to link to the Tech's ID and WeekEndingDate.

On the form, I thought I could have something like this:
NameField/ WeekEndingField/ DateField /LocationField/ NotesField

and by selecting a button that says "Add New Location', a new line appears

NameField/ WeekEndingField/ DateField2 /LocationField2/ NotesField2
etc.

All of the entries are populated in the Travel tables (Linked by the NameField and WeekEndingField).

I hope I am explaining this clearly, I am new to Access....

 

[David R]

Ok good, now that we know which it is we can get more specific.

Are you comfortable doing this through code? Because it's going to be quite a bit simpler for me to explain, and probably simpler for you overall...

A code button for going to a new record, at its simplest, looks about like this:

Private Sub buttonAddVisit_Click()
    DoCmd.OpenForm "TravelForm", , , , acFormAdd
End Sub

OR

Private Sub buttonAddVisit_Click()
    DoCmd.GoToRecord , , acNewRec
End Sub

depending on whether your button is on the TravelForm itself or some other form/subform.

What you'll want, then, is to add a reference to those datafields to help start the travel log entry for the user. I'm not entirely clear where that data will come from in your case, but I'll add a couple of examples to get you started:

Private Sub buttonAddVisit_Click()
    ''from another form
    DoCmd.OpenForm "TravelForm", , , , acFormAdd
    Forms!TravelForm!TechID = Me.TechID
    Forms!TravelForm!VisitDate = Today()
    Forms!TravelForm!Location = Me.comboLocation.Value
End Sub

OR

Private Sub buttonAddVisit_Click()
    ''from inside the form itself
    Dim LastLocation As String

    LastLocation = Me.Location
    DoCmd.GoToRecord , , acNewRec
    Me.TechID = fOSUsername()
    Me.VisitDate = fFindLastFriday()
    Me.Location = LastLocation
End Sub

Etc (this is, of course, all vaporcode; fOSUsername() is a pretty common public function to find the user's Windows username if you're not using Access logins (sometimes you can just use Environ("Username")), and fFindLastFriday would be a function to determine the date in question, if you feel you need to do that for the user)

 

[ohappyday]

Hello -

Sorry for the delay in responding...

Ok, I totally understand adding the code butt, but you are way over my head regarding references the fields.

Once the button is selected, I want the user to enter data in fields on the Travel form (linked to Travel Table).

The Travel Table may have mutiple entries for the Tech(Tech ID) for any particular week (WeekEndingID).

Will this code allow them to do so?

I apologize if I am confusing you....