Add record to form

[fcmsb13]

Hi,
I have a form with records. I need to be able to have a command button that opens a form and adds the record where the command button was clicked (continuous form) to that form. Picture having a main form then a little popup form that when you click the records in the main form it adds that record to the popup form's query. These records in the popup form query need to be editable.
Thanks for listening,
Paul

 

[Jonathan Kok]

So I'm understanding correctly...
You have a main form (FormA) in 'Single Form' format that gets its information from TableA. You have a second form (FormB) in 'Continuous Forms' format that gets its info from TableB. When you click on a button on FormA, you want to take some of the info from the current record in TableA and append it to TableB, then have FormB open, listing the new record as well as the other records in the table.

Is this right?

 

[fcmsb13]

yes, but the only records that are in formb are the ones that are selected.

 

[Jonathan Kok]

Wait...are both forms continuous? How are you selecting multiple records?

 

[fcmsb13]

Yes both forms are continuous and have made a checkbox next to each record.

Private Sub Command43_Click()
Set rs = Me.Recordset
Dim user As DAO.Database
Set user = DAO.OpenDatabase("c:\a\user.mdb")
Set rs3 = user.OpenRecordset("currentuser", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
If rs!dispositionchk <> 0 Then
    rs!status = "Do Not Call" 'change status to to be contacted
    rs!dnc = "DNC"
    rs!lastlo = rs!Assignedto
    rs!Assignedto = " "  'assignedto=current employee name
    rs!LDdate = Now()
    rs!LDdisposition = "Do Not Call"
'If I can attach some code here that appends this record to formB it would work great!
    rs.Update
    rsmyrs.Close
End If
rs.MoveNext
Loop
Me.Requery
End Sub

 

[Jonathan Kok]

Well, you seem to be doing fine. Similar to what you've got there, you would simply use the .addnew function of another recordset to add the record. Assuming the information you want to add to TableB is displayed on FormA, after you update the record you're editing, but before the if statement is ended, add something like:

set rs4 = openrecordset("tablename")
With rs4
.AddNew
!<fieldname> = <whatever>
!<fieldname2> = <whatever>
.Update
.Close
end with

If it's not displayed on FormA, you could make an 'Append' Query that pulls the relevant information from one table to another.

I feel I'm missing something, because from the code you've posted, you seem to have a good idea as to what you're doing.

EDIT: BTW, look into 'With' statements. They make coding quicker and more concise.

One quick question. Am I right in thinking that this database is referencing a second database?

 

[fcmsb13]

I think you might of misunderstood me. I am not looking to add records, i am just looking to have records from one form transfer to another form that pops up.
Yes I am referencing an external database because this is a shared database.

 

[Jonathan Kok]

OK. So this popup form displays those records selected in the first form? I assume "dispositionchk" is stored in the source table of formA? Could you not simply make the source query of FormB to show only those records that are checked? Wait...is the popup form always displayed? Is it just listing the selected records from FormA? If so, it should just be a matter of using [Forms]![formname].Requery.

 

[fcmsb13]

That might be exactly what I am looking for. How do I make the form that pops-up show the records that are checked in the main form.

 

[Jonathan Kok]

Is the checkbox stored in the table, or only displayed on the form? I'm assuming it's stored in the table. If so, set the source query of FormB to filter on it.

Make sure you save the record on FormA before you update FormB, though. "DoCmd.RunCommand acCmdSaveRecord"

 

[fcmsb13]

How do I set the recordsource of formB to the recordsource of formA. I cant make a main table the recordsource of formB and just filter where dispoitionchk = 1, Because it is a shared database.

 

[Jonathan Kok]

Why not?
Set the recordsource to a query that filters on dispositionchk.
I'm assuming you've linked the tables, so the table shows up in the table section of the DB. (I've gotta go, but I should be able to continue this later tonight).

 

[fcmsb13]

THere are many disposition that can have the effect on the record. So if one employee wants to set disposition a lead and checks it, but another employee at a different computer hits a disposition button to move a different lead, it will change all leads selected. VERY hard to explain.

 

[Jonathan Kok]

You may consider moving to 'batch processing'. I had to do this with a database myself. Kind of a PITA, but it worked; everyone was able to keep their info seperate.
Basically, when the user opens FormA, have a popup form appear that allows the user to select (or create) a batch. Data is stored in a table. Mine had the fields [BatchID], [batDate], [batDescription], [batInUse]. Store the batchID selected in the popup form in a text box in the header of FormA. Add a field to your main table for FormA to contain the BatchID. Whenever the user hits the 'disposition' button, have it update the BatchID in the source table with the selected BatchID. From there, you can have your FormB filter the results based on the users' selected BatchID.
It takes a bit of coding to work, but can be quite effective. I use the 'InUse' field as a checkbox control to prevent users from being able to select the same batch. Just remember to release the batchID on the OnClose event of FormA.