User Form adding records in 2 rows at a time

**sheikhmisbah** · 05-19-2012, 01:31 PM

Hi,

I created a user form & every thing is recording perfect, but i would like to add the information in 2 rows at a same time.
Example :

Date : 05/11/12
Part No. : ABC
Receiver : R1
Sender : S1
Quantity : 100

When i click the button to add the above records, have to add the following manner :

Date : Part No. Name Quantity
1. 05/11 ABC R1 100
2. 05/11 ABC S1 100

I have acoding for single record, but i would like to know who can i record using one form to record two different rows, but have all the information same except the name.

Waiting your reply.

Regards,

Misbah

**jaslake** · 05-19-2012, 03:34 PM

Hi Misbah
Why don't you show us you're existing code (be sure to use Code Tags).

**sheikhmisbah** · 05-19-2012, 05:31 PM

Hi Jaslake,

Thank you very much for your reply.
As per your request, following are the codes :

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find first empty row in database
''lRow = ws.Cells(Rows.Count, 1) _
'' .End(xlUp).Offset(1, 0).Row

'revised code to avoid problems with
'Excel lists and tables in newer versions
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.cboPart.ListIndex

'check for a part number
If Trim(Me.cboPart.Value) = "" Then
Me.cboPart.SetFocus
MsgBox "Please enter a part number"
End If
If Trim(Me.cboLocation.Value) = "" Then
Me.cboLocation.SetFocus
MsgBox "Please enter the Location"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.txtDate.Value
.Cells(lRow, 2).Value = Me.cboLocation.Value
.Cells(lRow, 4).Value = Me.cboPart.Value
.Cells(lRow, 5).Value = Me.cboPart.List(lPart, 1)
.Cells(lRow, 9).Value = Me.txtQty.Value
.Cells(lRow, 11).Value = Me.txtQty2.Value
.Cells(lRow, 7).Value = Me.txtQty3.Value
.Cells(lRow, 8).Value = Me.txtQty4.Value
'new addition

End With

'clear the data
Me.cboPart.Value = ""
Me.cboLocation.Value = ""
Me.cboLocation2.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = ""
Me.cboPart.SetFocus

End Sub

**jaslake** · 05-19-2012, 05:44 PM

Hi Misbah

Please wrap your code in Code Tags...the Moderators are rather strict on this.

Rule #3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). You can also type the code tags in manually if you prefer.

**sheikhmisbah** · 05-19-2012, 05:51 PM

Private Sub cmdAdd_Click()
 Dim lRow As Long
 Dim lPart As Long
 Dim ws As Worksheet
 Set ws = Worksheets("PartsData")
 
'find first empty row in database
 ''lRow = ws.Cells(Rows.Count, 1) _
 '' .End(xlUp).Offset(1, 0).Row
 
'revised code to avoid problems with
 'Excel lists and tables in newer versions
 lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
 SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

 lPart = Me.cboPart.ListIndex
 
'check for a part number
 If Trim(Me.cboPart.Value) = "" Then
 Me.cboPart.SetFocus
 MsgBox "Please enter a part number"
 End If
 If Trim(Me.cboLocation.Value) = "" Then
 Me.cboLocation.SetFocus
 MsgBox "Please enter the Location"
 Exit Sub
 End If
 
'copy the data to the database
 With ws
 .Cells(lRow, 1).Value = Me.txtDate.Value
 .Cells(lRow, 2).Value = Me.cboLocation.Value
 .Cells(lRow, 4).Value = Me.cboPart.Value
 .Cells(lRow, 5).Value = Me.cboPart.List(lPart, 1)
 .Cells(lRow, 9).Value = Me.txtQty.Value
 .Cells(lRow, 11).Value = Me.txtQty2.Value
 .Cells(lRow, 7).Value = Me.txtQty3.Value
 .Cells(lRow, 8).Value = Me.txtQty4.Value
 'new addition


 End With
 
'clear the data
 Me.cboPart.Value = ""
 Me.cboLocation.Value = ""
 Me.cboLocation2.Value = ""
 Me.txtDate.Value = Format(Date, "Medium Date")
 Me.txtQty.Value = ""
 Me.cboPart.SetFocus
 
End Sub

**jaslake** · 05-19-2012, 05:53 PM

Hi Misbah

Perfect.

**sheikhmisbah** · 05-19-2012, 05:55 PM

Thanks Jaslake

**jaslake** · 05-19-2012, 06:13 PM

Hi Misbah

Without seeing your workbook and UserForm, I'd suggest looking at this approach

 With ws
 .Cells(lRow, 1).Value = Me.txtDate.Value
 .Cells(lRow + 1, 1).Value = Me.txtDate.Value
 .Cells(lRow, 2).Value = Me.cboLocation.Value
 .Cells(lRow + 1, 2).Value = Me.cboLocation.Value
 .Cells(lRow, 4).Value = Me.cboPart.Value
 .Cells(lRow + 1, 4).Value = Me.cboPart.Value
 'Etc, etc
End With

If this doesn't work for you then I'll need to see the workbook with the UserForm and existing code. Let me know of issues.

**sheikhmisbah** · 05-20-2012, 02:03 PM

Thank you very much & I really appreciate for your help.

Best Regards,

Mibah

**jaslake** · 05-20-2012, 02:53 PM

Hi Misbah

You're welcome...glad I could help. If this satisfies your need, please mark your Thread as Solved.

User Form adding records in 2 rows at a time

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

User Form adding records in 2 rows at a time

Re: User Form adding records in 2 rows at a time

Re: User Form adding records in 2 rows at a time

Re: User Form adding records in 2 rows at a time

Re: User Form adding records in 2 rows at a time

Re: User Form adding records in 2 rows at a time

Re: User Form adding records in 2 rows at a time

Re: User Form adding records in 2 rows at a time

Re: User Form adding records in 2 rows at a time

Re: User Form adding records in 2 rows at a time

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions